省市县单表级联表变平铺视图的sql解决

在一个Java群里看到有工程师提问，怎样把省市县单表级联表变平铺视图。

也就是district(id,father_id,name), father_id=0即为省直辖市，变成为county(province_id, province, city_id, city,county_id,county)

解：

建立省视图：

create view v_province as

select id as province_id, name as province from district where father_id=0;

建立市视图：

create view v_city as

select  province_id,province,id as city_id , name as city  from district a, v_province b where a.father_id=b.province_id;

建立县视图：

create view v_county as

select province_id,province,city_id , city , id as county_id, name as county

from district a, v_city b

where a.father_id=b.city_id;