首先,我们新建一张测试用的临时表#country,其中包含三个字段,AreaNam(地名) ,BelongTo(上级地名) ,Msg(地方简介)
Create table #country (AreaNam NVARCHAR(10),BelongTo Nvarchar(10),Msg varchar(100))
下面我们往这张表里插入一堆测试数据:
INSERT INTO #country
SELECT '中国','中国',null union all
SELECT '江苏','中国',null union all
SELECT '南京','江苏',null union all
SELECT '无锡','江苏',null union all
SELECT '徐州','江苏',null union all
SELECT '扬州','江苏',null union all
SELECT '苏州','江苏',null union all
SELECT '六合区','南京',null union all
SELECT '江宁区','南京',null union all
SELECT '浦口区','南京',null union all
SELECT '仙林区','南京',null union all
SELECT '建邺区','南京',null union all
SELECT '宝应','扬州',null union all
SELECT '仪征','扬州',null union all
SELECT '小官庄','宝应',null union all
SELECT '范水','宝应',null union all
SELECT '鲁垛','宝应',null union all
SELECT '安宜','宝应',null union all
SELECT '组全','小官庄',null union all
SELECT '房桥','小官庄',null union all
SELECT '直下沟','小官庄',null union all
SELECT '山东','中国',null union all
SELECT '济南','山东',null union all
SELECT '青岛','山东',null union all
SELECT '淄博','山东',null union all
SELECT '烟台','山东',null union all
SELECT '张店','淄博',null union all
SELECT '博山','淄博',null union all
SELECT '淄川','淄博',null union all
SELECT '龙王山','浦口区',null union all
SELECT '高新区','浦口区',null union all
SELECT '陆军指挥学院','浦口区',null union all
SELECT '南京信息工程大学','浦口区',null union all
SELECT '金陵学院','浦口区',null
到这里,表结构已经完成了,这张表的表结构应该不难理解。
但是下面问题来了,有要求查找出南京包含南京以内(属于南京)的所有地名,因为如果数据量比较大的话,我们根本无法确定一个城市往下分了多少级地名,如果分的层级太多的话,使用循环取实现查询结果也是一个可行的方案,sql如下:
DECLARE @CITY NVARCHAR(MAX)='南京'
Create table #TEMP (AreaNam NVARCHAR(10),BelongTo Nvarchar(10),Msg varchar(100))
Create table #tempAreaname (AreaNam NVARCHAR(10))
Create table #tempAreanametemp (AreaNam NVARCHAR(10))
INSERT INTO #tempAreaname
SELECT @CITY
INSERT INTO #TEMP
SELECT * FROM #country WHERE AreaNam=@CITY
WHILE 1=1
BEGIN
insert into #tempAreanametemp
SELECT areanam FROM #country WHERE belongto IN(select areanam from #tempAreaname)
IF @@ROWCOUNT<>0
BEGIN
INSERT INTO #TEMP
SELECT * FROM #country WHERE belongto IN(select areanam from #tempAreaname)
delete from #tempAreaname
insert into #tempAreaname
select * from #tempAreanametemp
delete from #tempAreanametemp
END
ELSE
BEGIN
SELECT * FROM #TEMP
DROP TABLE #TEMP
DROP TABLE #tempAreaname
DROP TABLE #tempAreanametemp
RETURN
END
END
查询结果如下:
AreaNam BelongTo Msg
南京 江苏 NULL
六合区 南京 NULL
江宁区 南京 NULL
浦口区 南京 NULL
仙林区 南京 NULL
建邺区 南京 NULL
龙王山 浦口区 NULL
高新区 浦口区 NULL
陆军指挥学院 浦口区 NULL
南京信息工程大学 浦口区 NULL
金陵学院 浦口区 NULL
这正是我们所想要的结果,但是总感觉这写法太复杂而且执行效率也不是很高,当然啦用循环去写的话肯定也有简单一点的写法的,这不是我们今天的重点。今天的重点是用CTE递归的方式去实现我们所想要的结果,SQL如下:
WITH CTE AS (
SELECT AreaNam,BelongTo,Msg FROM #country WHERE AreaNam='南京'
UNION ALL
SELECT A.AreaNam,A.BelongTo,A.Msg FROM #country A INNER JOIN CTE B ON A.BelongTo=B.AreaNam
)
SELECT * FROM CTE
查询结果也是一样一样的:
AreaNam BelongTo Msg
南京 江苏 NULL
六合区 南京 NULL
江宁区 南京 NULL
浦口区 南京 NULL
仙林区 南京 NULL
建邺区 南京 NULL
龙王山 浦口区 NULL
高新区 浦口区 NULL
陆军指挥学院 浦口区 NULL
南京信息工程大学 浦口区 NULL
金陵学院 浦口区 NULL