SqlServer 省市县三级联动
三张表递归合并成一张表sql如下：
insert into table2(area_name,area_parent_id)
select province,'0' from provinces
insert into table2(area_name,area_parent_id)
select city,c.id from cities a
inner join provinces b on a.provinceid=b.provinceid
inner join table2 c on b.province=c.area_name
insert into table2(area_name,area_parent_id)
select a.area,c.id from areas a
inner join cities b on a.cityid=b.cityid
inner join table2 c on b.city=c.area_name and area_parent_id<>0
select * from table2
上面的后来在项目中发现有重复数据改了一下如下：
-- area_parent_id
insert into [table6](id,area_name,area_parent_id)--插入直辖市
--select provinceid,province from provinces
--SELECT CONVERT(VARCHAR(10),ProvinceID) + CONVERT(VARCHAR(10),CITYID),City,ProvinceID FROM cities --插入省市
SELECT CONVERT(VARCHAR(10),a.CityID) + CONVERT(VARCHAR(10),a.id),a.area,a.CityID --插入县区
FROM areas A INNER JOIN cities B ON A.CityID = B.CityID
把注释去掉一个一个来执行最后结果就是想要的。
参考地址：http://bbs.csdn.net/topics/340224940
下面是三张表数据下载地址：
http://files.cnblogs.com/files/sunqiang/%E5%85%A8%E5%9B%BD%E7%9C%81%E5%B8%82%E5%8C%BA%E8%81%94%E5%8A%A8%E6%95%B0%E6%8D%AE%E5%BA%93sql%E6%A0%BC%E5%BC%8F%28MySQL%29.rar