各级省份与对应的地级市SQL语句（优化）

--省份表 if exists(select * from sysobjects where name='business_Province') drop table business_Province create table business_Province ( P_Id int primary key, P_Name nvarchar(8) NOT NULL,--省份名称 firstName nvarchar(2) NOT NULL,--省份名称 ) insert into business_Province (P_Id,P_Name,firstName) select '1',N'河北省','h' union all select '2',N'河南省','h' union all select '3',N'山东省','s' union all select '4',N'山西省','s' union all select '5',N'辽宁省','l' union all select '6',N'黑龙江省','h' union all select '7',N'吉林省','j' union all select '8',N'甘肃省','g' union all select '9',N'青海省','q' union all select '10',N'江苏省','j' union all select '11',N'江西省','j' union all select '12',N'湖北省','h' union all select '13',N'湖南省','h' union all select '14',N'浙江省','z' union all select '15',N'广东省','g' union all select '16',N'云南省','y' union all select '17',N'福建省','f' union all select '18',N'海南省','h' union all select '19',N'四川省','s' union all select '20',N'陕西省','s' union all select '21',N'贵州省','g' union all select '22',N'安徽省','a' union all select '23',N'台湾省','z1' union all select '24',N'重庆市','c' union all select '25',N'北京市','b' union all select '26',N'上海市','s' union all select '27',N'天津市','t' union all select '28',N'广西壮族自治区','g' union all select '29',N'内蒙古自治区','n' union all select '30',N'西藏自治区','x' union all select '31',N'新疆维吾尔自治区','x' union all select '32',N'宁夏回族自治区','n' union all select '33',N'澳门特别行政区','z1' union all select '34',N'香港特别行政区','z1' select * from business_Province order by firstName --市级表 if exists(select * from sysobjects where name='business_City') drop table business_City create table business_City ( P_Id int, C_Id int, C_Name nvarchar(20) primary key NOT NULL,--市级名称 ) --河北 insert into business_City (P_Id,C_Id,C_Name) select '1','1',N'石家庄' union all select '1','2',N'唐山' union all select '1','3',N'秦皇岛' union all select '1','4',N'邯郸' union all select '1','5',N'邢台' union all select '1','6',N'保定' union all select '1','7',N'张家口' union all select '1','8',N'承德' union all select '1','9',N'沧州' union all select '1','10',N'廊坊' union all select '1','11',N'衡水' --河南 insert into business_City (P_Id,C_Id,C_Name) select '2','1',N'郑州' union all select '2','2',N'开封' union all select '2','3',N'洛阳' union all select '2','4',N'南阳' union all select '2','5',N'漯河' union all select '2','6',N'许昌' union all select '2','7',N'三门峡' union all select '2','8',N'平顶山' union all select '2','9',N'周口' union all select '2','10',N'驻马店' union all select '2','11',N'新乡' union all select '2','12',N'鹤壁' union all select '2','13',N'焦作' union all select '2','14',N'濮阳' union all select '2','15',N'安阳' union all select '2','16',N'商丘' union all select '2','17',N'信阳' --山东 insert into business_City (P_Id,C_Id,C_Name) select '3','1',N'济南' union all select '3','2',N'青岛' union all select '3','3',N'烟台' union all select '3','4',N'威海' union all select '3','5',N'东营' union all select '3','6',N'淄博' union all select '3','7',N'潍坊' union all select '3','8',N'日照' union all select '3','9',N'莱芜' union all select '3','10',N'菏泽' union all select '3','11',N'枣庄' union all select '3','12',N'德州' union all select '3','13',N'滨州' union all select '3','14',N'临沂' union all select '3','15',N'济宁' union all select '3','16',N'聊城' union all select '3','17',N'泰安' --山西省 insert into business_City (P_Id,C_Id,C_Name) select '4','1',N'太原' union all select '4','2',N'大同' union all select '4','3',N'朔州' union all select '4','4',N'阳泉' union all select '4','5',N'长治' union all select '4','6',N'忻州' union all select '4','7',N'吕梁' union all select '4','8',N'晋中' union all select '4','9',N'临汾' union all select '4','10',N'运城' union all select '4','11',N'晋城' --辽宁省 insert into business_City (P_Id,C_Id,C_Name) select '5','1',N'沈阳' union all select '5','2',N'大连' union all select '5','3',N'鞍山' union all select '5','4',N'抚顺' union all select '5','5',N'本溪' union all select '5','6',N'丹东' union all select '5','7',N'锦州' union all select '5','8',N'营口' union all select '5','9',N'阜新' union all select '5','10',N'辽阳' union all select '5','11',N'盘锦' union all select '5','12',N'铁岭' union all select '5','13',N'朝阳' union all select '5','14',N'葫芦岛' --黑龙江省 insert into business_City (P_Id,C_Id,C_Name) select '6','1',N'哈尔滨' union all select '6','2',N'齐齐哈尔' union all select '6','3',N'牡丹江' union all select '6','4',N'佳木斯' union all select '6','5',N'大庆' union all select '6','6',N'鸡西' union all select '6','7',N'双鸭山' union all select '6','8',N'鹤岗' union all select '6','9',N'绥化' union all select '6','10',N'七台河' union all select '6','11',N'伊春' union all select '6','12',N'黑河' --吉林省 insert into business_City (P_Id,C_Id,C_Name) select '7','1',N'长春' union all select '7','2',N'吉林' union all select '7','3',N'四平' union all select '7','4',N'辽源' union all select '7','5',N'通化' union all select '7','6',N'白山' union all select '7','7',N'松原' union all select '7','8',N'白城' --甘肃省 INSERT INTO business_City VALUES ('8','1',N'兰州'); INSERT INTO business_City VALUES ('8','2',N'嘉峪关'); INSERT INTO business_City VALUES ('8','3',N'金昌'); INSERT INTO business_City VALUES ('8','4',N'白银'); INSERT INTO business_City VALUES ('8','5',N'天水'); INSERT INTO business_City VALUES ('8','6',N'酒泉'); INSERT INTO business_City VALUES ('8','7',N'张掖'); INSERT INTO business_City VALUES ('8','8',N'武威'); INSERT INTO business_City VALUES ('8','9',N'定西'); INSERT INTO business_City VALUES ('8','10',N'陇南'); INSERT INTO business_City VALUES ('8','11',N'平凉'); INSERT INTO business_City VALUES ('8','12',N'庆阳'); --青海省 INSERT INTO business_City VALUES ('9','1',N'西宁'); INSERT INTO business_City VALUES ('9','2',N'海东'); --江苏省 INSERT INTO business_City VALUES ('10','1',N'南京'); INSERT INTO business_City VALUES ('10','2',N'无锡'); INSERT INTO business_City VALUES ('10','3',N'徐州'); INSERT INTO business_City VALUES ('10','4',N'常州'); INSERT INTO business_City VALUES ('10','5',N'苏州'); INSERT INTO business_City VALUES ('10','6',N'南通'); INSERT INTO business_City VALUES ('10','7',N'连云港'); INSERT INTO business_City VALUES ('10','8',N'淮安'); INSERT INTO business_City VALUES ('10','9',N'盐城'); INSERT INTO business_City VALUES ('10','10',N'扬州'); INSERT INTO business_City VALUES ('10','11',N'镇江'); INSERT INTO business_City VALUES ('10','12',N'泰州'); INSERT INTO business_City VALUES ('10','13',N'宿迁'); --江西省 insert into business_City (P_Id,C_Id,C_Name) select '11','1',N'南昌' union all select '11','2',N'景德镇' union all select '11','3',N'萍乡' union all select '11','4',N'九江' union all select '11','5',N'鹰潭' union all select '11','6',N'赣州' union all select '11','7',N'吉安' union all select '11','8',N'宜春' union all select '11','9',N'抚州' union all select '11','10',N'新余' union all select '11','11',N'上饶' --湖北 INSERT INTO business_City VALUES ('12','1',N'武汉'); INSERT INTO business_City VALUES ('12','2',N'黄石'); INSERT INTO business_City VALUES ('12','3',N'十堰'); INSERT INTO business_City VALUES ('12','4',N'荆州'); INSERT INTO business_City VALUES ('12','5',N'宜昌'); INSERT INTO business_City VALUES ('12','6',N'襄阳'); INSERT INTO business_City VALUES ('12','7',N'鄂州'); INSERT INTO business_City VALUES ('12','8',N'荆门'); INSERT INTO business_City VALUES ('12','9',N'黄冈'); INSERT INTO business_City VALUES ('12','10',N'孝感'); INSERT INTO business_City VALUES ('12','11',N'咸宁'); INSERT INTO business_City VALUES ('12','12',N'随州'); --湖南 INSERT INTO business_City VALUES ('13','1',N'长沙'); INSERT INTO business_City VA