SQLServer CTE递归

转自https://blog.youkuaiyun.com/wikey_zhang/article/details/72639546

首先，我们新建一张测试用的临时表#country，其中包含三个字段，AreaNam（地名） ，BelongTo（上级地名） ，Msg（地方简介）

[sql]  view plain  copy

1. Create table #country (AreaNam NVARCHAR(10),BelongTo Nvarchar(10),Msg varchar(100))  

下面我们往这张表里插入一堆测试数据：

[sql]  view plain  copy

1. INSERT INTO #country  
2. SELECT '中国','中国',null union all  
3. SELECT '江苏','中国',null union all  
4. SELECT '南京','江苏',null union all  
5. SELECT '无锡','江苏',null union all  
6. SELECT '徐州','江苏',null union all  
7. SELECT '扬州','江苏',null union all  
8. SELECT '苏州','江苏',null union all  
9. SELECT '六合区','南京',null union all  
10. SELECT '江宁区','南京',null union all  
11. SELECT '浦口区','南京',null union all  
12. SELECT '仙林区','南京',null union all  
13. SELECT '建邺区','南京',null union all  
14. SELECT '宝应','扬州',null union all  
15. SELECT '仪征','扬州',null union all  
16. SELECT '小官庄','宝应',null union all  
17. SELECT '范水','宝应',null union all  
18. SELECT '鲁垛','宝应',null union all  
19. SELECT '安宜','宝应',null union all  
20. SELECT '组全','小官庄',null union all  
21. SELECT '房桥','小官庄',null union all  
22. SELECT '直下沟','小官庄',null union all  
23. SELECT '山东','中国',null union all  
24. SELECT '济南','山东',null union all  
25. SELECT '青岛','山东',null union all  
26. SELECT '淄博','山东',null union all  
27. SELECT '烟台','山东',null union all  
28. SELECT '张店','淄博',null union all  
29. SELECT '博山','淄博',null union all  
30. SELECT '淄川','淄博',null union all  
31. SELECT '龙王山','浦口区',null union all  
32. SELECT '高新区','浦口区',null union all  
33. SELECT '陆军指挥学院','浦口区',null union all  
34. SELECT '南京信息工程大学','浦口区',null union all  
35. SELECT '金陵学院','浦口区',null  

到这里，表结构已经完成了，这张表的表结构应该不难理解。

但是下面问题来了，有要求查找出南京包含南京以内（属于南京）的所有地名，因为如果数据量比较大的话，我们根本无法确定一个城市往下分了多少级地名，如果分的层级太多的话，使用循环取实现查询结果也是一个可行的方案，sql如下：

[sql]  view plain  copy

1. DECLARE @CITY NVARCHAR(MAX)='南京'  
2. Create table #TEMP (AreaNam NVARCHAR(10),BelongTo Nvarchar(10),Msg varchar(100))  
3. Create table #tempAreaname (AreaNam NVARCHAR(10))  
4. Create table #tempAreanametemp (AreaNam NVARCHAR(10))  
5. INSERT INTO #tempAreaname   
6. SELECT @CITY  
7. INSERT INTO #TEMP  
8. SELECT * FROM #country WHERE AreaNam=@CITY  
9. WHILE 1=1  
10. BEGIN  
11.     insert into #tempAreanametemp  
12.     SELECT areanam  FROM #country WHERE belongto IN(select areanam from #tempAreaname)  
13.   
14.     IF @@ROWCOUNT<>0  
15.     BEGIN   
16.         INSERT INTO #TEMP  
17.         SELECT * FROM #country WHERE belongto IN(select areanam from #tempAreaname)  
18.         delete from #tempAreaname  
19.         insert into #tempAreaname  
20.         select * from #tempAreanametemp  
21.         delete from #tempAreanametemp         
22.     END  
23.     ELSE  
24.     BEGIN     
25.         SELECT * FROM #TEMP  
26.         DROP TABLE #TEMP  
27.         DROP TABLE #tempAreaname  
28.         DROP TABLE #tempAreanametemp  
29.         RETURN  
30.     END  
31. END  

查询结果如下：

AreaNam BelongTo Msg
南京 江苏 NULL
六合区 南京 NULL
江宁区 南京 NULL
浦口区 南京 NULL
仙林区 南京 NULL
建邺区 南京 NULL
龙王山 浦口区 NULL
高新区 浦口区 NULL
陆军指挥学院 浦口区 NULL
南京信息工程大学 浦口区 NULL
金陵学院 浦口区 NULL

这正是我们所想要的结果，但是总感觉这写法太复杂而且执行效率也不是很高，当然啦用循环去写的话肯定也有简单一点的写法的，这不是我们今天的重点。今天的重点是用CTE递归的方式去实现我们所想要的结果,SQL如下：

[sql]  view plain  copy

1. WITH CTE AS (  
2. SELECT AreaNam,BelongTo,Msg FROM #country WHERE AreaNam='南京'  
3. UNION ALL  
4. SELECT  A.AreaNam,A.BelongTo,A.Msg FROM #country A INNER JOIN CTE B ON A.BelongTo=B.AreaNam  
5. )  
6. SELECT * FROM CTE  

[sql]  view plain  copy

1.   

[sql]  view plain  copy

1. 查询结果也是一样一样的：  

[sql]  view plain  copy

1. AreaNam BelongTo Msg  
2. 南京 江苏 NULL  
3. 六合区 南京 NULL  
4. 江宁区 南京 NULL  
5. 浦口区 南京 NULL  
6. 仙林区 南京 NULL  
7. 建邺区 南京 NULL  
8. 龙王山 浦口区 NULL  
9. 高新区 浦口区 NULL  
10. 陆军指挥学院 浦口区 NULL  
11. 南京信息工程大学 浦口区 NULL  
12. 金陵学院 浦口区 NULL