数据库地域脚本整理

/*插入第一级地区名：默认亚洲*/
insert into cms.cms_tag_area(tag_id,tag_name,parent_tag_id,tag_status,create_time,level)
values('11','亚洲',null,'enable',now(),0);


/*插入第二级地区名：默认中国*/
insert into cms.cms_tag_area(tag_id,tag_name,parent_tag_id,tag_status,create_time,level)
values('1111','中国','11','enable',now(),1);


/*插入第三级地区名：市级*/
insert into cms.cms_tag_area(tag_id,tag_name,parent_tag_id,tag_status,create_time,level)
(select concat(1111,sc.code),sc.addr,'1111','enable',now(),CAST(sc.level AS SIGNED)+1 
from zhj.state_code sc where sc.level = 1 );


/*插入第四级地区名：县。管辖级*/
insert into cms.cms_tag_area(tag_id,tag_name,parent_tag_id,tag_status,create_time,level)
(select concat(1111,substring(sc.code,1,4)),sc.addr,concat(1111,substring(sc.pcode,1,2)),'disable',now(),CAST(sc.level AS SIGNED)+1 
 from zhj.state_code sc where sc.level = 2);


/*插入第五级地区名：区级*/
insert into cms.cms_tag_area(tag_id,tag_name,parent_tag_id,tag_status,create_time,level)
(select concat(1111,substring(sc.code,1,6)),sc.addr,concat(1111,substring(sc.pcode,1,4)),'disable',now(),CAST(sc.level AS SIGNED)+1 
 from zhj.state_code sc where sc.level = 3);






/*插入州*/
insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
values('12','欧洲',null,'disable',now(),0,null);
insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
values('13','美洲',null,'disable',now(),0,null);
insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
values('14','大洋洲',null,'disable',now(),0,null);
insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
values('15','非洲',null,'disable',now(),0,null);




/*插入国家*/
insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select DISTINCT concat(11,substring(fd.country_id,3,2)),country,'11','disable',now(),1,null from zhj_test.foreigndata fd where continent='亚洲');


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select DISTINCT concat(12,substring(fd.country_id,3,2)),country,'12','disable',now(),1,null from zhj_test.foreigndata fd where continent='欧洲');


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select DISTINCT concat(13,substring(fd.country_id,3,2)),country,'13','disable',now(),1,null from zhj_test.foreigndata fd where continent='美洲');


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select DISTINCT concat(14,substring(fd.country_id,3,2)),country,'14','disable',now(),1,null from zhj_test.foreigndata fd where continent='大洋洲');


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select DISTINCT concat(15,substring(fd.country_id,3,2)),country,'15','disable',now(),1,null from zhj_test.foreigndata fd where continent='非洲');




/*插入省份*/
insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select CONCAT('11',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),
province,concat(11,substring(fd.country_id,3,2)),'disable',now(),2,null
from zhj_test.foreigndata  fd where continent='亚洲'
);


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select CONCAT('12',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),
province,concat(12,substring(fd.country_id,3,2)),'disable',now(),2,null
from zhj_test.foreigndata  fd where continent='欧洲'
);


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select CONCAT('13',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),
province,concat(13,substring(fd.country_id,3,2)),'disable',now(),2,null
from zhj_test.foreigndata  fd where continent='美洲'
);


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select CONCAT('14',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),
province,concat(14,substring(fd.country_id,3,2)),'disable',now(),2,null
from zhj_test.foreigndata  fd where continent='大洋洲'
);


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select CONCAT('15',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),
province,concat(15,substring(fd.country_id,3,2)),'disable',now(),2,null
from zhj_test.foreigndata  fd where continent='非洲'
);






/*插入城市*/
insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select CONCAT(CONCAT('11',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),right(CONCAT('0',cast(province_id as SIGNED)),2)),
city,CONCAT('11',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),'disable',now(),3,null
from zhj_test.foreigndata where continent='亚洲');


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select CONCAT(CONCAT('12',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),right(CONCAT('0',cast(province_id as SIGNED)),2)),
city,CONCAT('12',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),'disable',now(),3,null
from zhj_test.foreigndata where continent='欧洲');


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select CONCAT(CONCAT('13',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),right(CONCAT('0',cast(province_id as SIGNED)),2)),
city,CONCAT('13',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),'disable',now(),3,null
from zhj_test.foreigndata where continent='美洲');


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select CONCAT(CONCAT('14',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),right(CONCAT('0',cast(province_id as SIGNED)),2)),
city,CONCAT('14',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),'disable',now(),3,null
from zhj_test.foreigndata where continent='大洋洲');


insert into cms.cms_tag_area_test(tag_id,tag_name,parent_tag_id,tag_status,create_time,level,alias)
(select CONCAT(CONCAT('15',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),right(CONCAT('0',cast(province_id as SIGNED)),2)),
city,CONCAT('15',CONCAT(substring(country_id,3,2),right(CONCAT('0',cast(province_id as SIGNED)),2))),'disable',now(),3,null
from zhj_test.foreigndata where continent='非洲');






update zhj_test.foreigndata set country_id='5031.0' where country_id='6001.0';
update zhj_test.foreigndata set country_id='5032.0' where country_id='6002.0';
update zhj_test.foreigndata set country_id='5033.0' where country_id='6003.0';
update zhj_test.foreigndata set country_id='5034.0' where country_id='6008.0';
update zhj_test.foreigndata set country_id='5035.0' where country_id='6004.0';
update zhj_test.foreigndata set country_id='5036.0' where country_id='6005.0';
update zhj_test.foreigndata set country_id='5037.0' where country_id='6007.0';
update zhj_test.foreigndata set country_id='5038.0' where country_id='6009.0';
update zhj_test.foreigndata set country_id='5039.0' where country_id='6010.0';
update zhj_test.foreigndata set country_id='5040.0' where country_id='6011.0';
update zhj_test.foreigndata set country_id='5041.0' where country_id='6012.0';






delete from zhj_test.foreigndata where country='法国' and province not in(
'法兰西岛','香槟-阿登','皮卡第','上诺曼底','中央-卢瓦尔谷Centre-Val','下诺曼底','勃艮第','北部-加莱海峡',
'洛林','阿尔萨斯','弗朗什孔泰','卢瓦尔河地区','布列塔尼','普瓦图-夏朗德','南部-比利牛斯',
'利穆赞','奥弗涅','朗格多克-鲁西永Languedoc-Roussillon（南部-比利牛斯-朗格多克-鲁西永）','普罗旺斯-阿尔卑斯-蓝色海岸Provence-Alpes-C?te','科西嘉');