SQLServer CTE递归和循环对比的优势--典型案例

首先，我们新建一张测试用的临时表#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