数据库mysql/sqlite将多个分表数据插入到总表

时间:2024-04-06 22:18:06

建立总表,将多个分表数据插入到总表:
思路:
采用先插入后更新的方式。后期查询本表的方式来填充总表数据。

如下截图:其中chinaadministrativepoint是总表,目的是把其他分表的数据汇总到chinaadministrativepoint总表中来。
原来的其中一个分表:
数据库mysql/sqlite将多个分表数据插入到总表

  1. insert into chinaadministrativepoint (code, province) select code,name from province;

  2. insert into chinaadministrativepoint(code, city, parent_id) select code, name, provinceCode from city;
    数据库mysql/sqlite将多个分表数据插入到总表

  3. update chinaadministrativepoint set province=(select name from province as e where chinaadministrativepoint.parent_id=e.code)where city is not null ;
    数据库mysql/sqlite将多个分表数据插入到总表

  4. insert into chinaadministrativepoint(code, area, lon, lat, parent_id)
    select code, name, lon, lat , cityCode from area;

  5. update chinaadministrativepoint set city=(select name from city as c where
    parent_id=c.code) where area is not null ;

  6. update chinaadministrativepoint set province=(select province from chinaadministrativepoint as e where
    chinaadministrativepoint.parent_id=e.code) where area is not null ;

  7. insert into chinaadministrativepoint(code, street, lon, lat, parent_id)
    select code, name, lon, lat , areaCode from street;

  8. 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 ;
    数据库mysql/sqlite将多个分表数据插入到总表
    9. insert into chinaadministrativepoint(code, village, lon, lat, parent_id)
    select code, name, lon, lat , streetCode from village;

  9. 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 ;
    数据库mysql/sqlite将多个分表数据插入到总表