建立总表,将多个分表数据插入到总表:
思路:
采用先插入后更新的方式。后期查询本表的方式来填充总表数据。
如下截图:其中chinaadministrativepoint是总表,目的是把其他分表的数据汇总到chinaadministrativepoint总表中来。
原来的其中一个分表:
-
insert into chinaadministrativepoint (code, province) select code,name from province;
-
insert into chinaadministrativepoint(code, city, parent_id) select code, name, provinceCode from city;
-
update chinaadministrativepoint set province=(select name from province as e where chinaadministrativepoint.parent_id=e.code)where city is not null ;
-
insert into chinaadministrativepoint(code, area, lon, lat, parent_id)
select code, name, lon, lat , cityCode from area; -
update chinaadministrativepoint set city=(select name from city as c where
parent_id=c.code) where area is not null ; -
update chinaadministrativepoint set province=(select province from chinaadministrativepoint as e where
chinaadministrativepoint.parent_id=e.code) where area is not null ; -
insert into chinaadministrativepoint(code, street, lon, lat, parent_id)
select code, name, lon, lat , areaCode from street; -
update chinaadministrativepoint set province=(select province from chinaadministrativepoint as e where
chinaadministrativepoint.parent_id=e.code), city=(select city from chinaadministrativepoint as c where
chinaadministrativepoint.parent_id=c.code), area=(select area from chinaadministrativepoint as d where
chinaadministrativepoint.parent_id=d.code) where street is not null ;
9. insert into chinaadministrativepoint(code, village, lon, lat, parent_id)
select code, name, lon, lat , streetCode from village; -
update chinaadministrativepoint set province=(select province from chinaadministrativepoint as e where
chinaadministrativepoint.parent_id=e.code), city=(select city from chinaadministrativepoint as c where
chinaadministrativepoint.parent_id=c.code), area=(select area from chinaadministrativepoint as d where
chinaadministrativepoint.parent_id=d.code), street=(select street from chinaadministrativepoint as g
where chinaadministrativepoint.parent_id=g.code) where village is not null ;