Sql CTE 递归的实际应用

什么是递归？
递归就是在运行的过程中调用自己，并且是有终止条件的。

举个栗子
有如下一张表T

希望得到如下结果

--建表
if object_id('T','U')is not null drop table T
CREATE TABLE T(
ID INT,
[NAME] VARCHAR(10),
ParentId INT
)
INSERT INTO T VALUES (1,'北京市',0);
INSERT INTO T VALUES (2,'广东省',0);
INSERT INTO T VALUES (3,'昌平区',1);
INSERT INTO T VALUES (4,'海淀区',1);
INSERT INTO T VALUES (5,'广州市',2);
INSERT INTO T VALUES (6,'深圳市',2);
INSERT INTO T VALUES (7,'沙河镇',3);
INSERT INTO T VALUES (8,'中关村',4);
INSERT INTO T VALUES (9,'天河区',5);
INSERT INTO T VALUES (10,'福田区',6);
select * from T
--递归部分
WITH CTE AS (
    SELECT ID,NAME,ParentId,1 AS Level 
    FROM T 
    WHERE parentId = 0  --定位点成员
    UNION ALL
    SELECT t.id,t.NAME,t.parentId,cte.Level+1 AS Level    	
    FROM T t
    JOIN CTE 
    ON t.parentId = CTE.id   --递归成员
)
SELECT 
     t1.name AS [一级地名]
    ,t2.name AS [二级地名]
    ,t3.name AS [三级地名]
FROM 
(SELECT * FROM CTE WHERE LEVEL=1) AS t1
INNER JOIN 
(SELECT * FROM CTE WHERE LEVEL=2) AS t2 ON t1.id=t2.parentId
INNER JOIN
(SELECT * FROM CTE WHERE LEVEL=3) AS t3 ON t2.id=t3.parentId
ORDER BY 1,2,3

---

如何理解递归部分?

递归 CTE 定义至少必须包含两个 CTE 查询定义
（1）定位点成员
（2）递归成员
可以定义多个定位点成员和递归成员；但必须将所有定位点成员查询定义置于第一个递归成员定义之前。 所有 CTE 查询定义都是定位点成员，但它们引用 CTE 本身时除外。

定位点成员必须与以下集合运算符之一结合使用：
① UNION ALL
② UNION
③ INTERSECT
④ EXCEPT
在最后一个定位点成员和第一个递归成员之间，以及组合多个递归成员时，只能使用 UNION ALL 集合运算符。

定位点成员和递归成员中的列数必须一致。

递归成员中列的数据类型必须与定位点成员中相应列的数据类型一致。

递归成员的 FROM 子句只能引用一次 CTE expression_name。

在递归成员的 CTE_query_definition 中不能出现下列项：
① SELECT DISTINCT
② GROUP BY
③ PIVOT（当数据库兼容性级别为 110 或更高级别时。请参阅 SQL Server 2016 中数据库引擎功能的中断性变更。）
④ HAVING
⑤ 标量聚合
⑥ TOP
⑦ LEFT、RIGHT、OUTER JOIN（允许使用 INNER JOIN）
⑧ 子查询
⑨ 应用于 CTE_query_definition 中 CTE 的递归引用的提示。

如果递归 CTE 组合不正确，可能会导致无限循环。
例如，如果递归成员查询定义对父列和子列返回相同的值，则会造成无限循环。
可以使用 MAXRECURSION 提示以及在 INSERT、UPDATE、DELETE 或 SELECT 语句的 OPTION 子句中的一个 0 到 32,767 之间的值，来限制特定语句所允许的递归级数，以防止出现无限循环。 这样就能够在解决产生循环的代码问题之前控制语句的执行。 服务器范围的默认值为 100。 如果指定 0，则没有限制。 每个语句只能指定一个 MAXRECURSION 值。 有关详细信息，请参阅 查询提示 (Transact-SQL)。

---

with CTE AS (
    SELECT 1 AS NUM
    UNION ALL
    SELECT T1.NUM + 1  FROM CTE T1
    WHERE NUM < 12
)
select * from CTE
Option(MAXRECURSION 100)  --设置最大的递归次数

查询结果如下：

生成一张时间维度表：

with CTE as
    (
        select cast('2015-1-9' as date) Wkdate
             ,cast('2015-1-3' as date ) as WkStDate
             ,cast('2015-1-9' as date ) as WkEdDate
			  ,year( cast('2015-1-1' as date)) [Year]
			  ,month(cast('2015-1-1' as date))[Month]
			  ,CASE DATEPART(QUARTER, cast('2015-1-1' as date))
				   WHEN 1 THEN N'Q1'
				   WHEN 2 THEN N'Q2'
				   WHEN 3 THEN N'q3'
				   ELSE N'Q4' END AS [Quarter]
			  ,cast (datename(W, cast('2015-1-1' as date))
				  as varchar(10)) AS [WeekDay]
			  ,N'W'+CAST( DATEPART(WEEK, cast('2015-1-1' as date)) AS VARCHAR(2)) AS [Week]
        union all
        select dateadd(day,7,Wkdate)
             ,dateadd(day,7,WkStdate)
              ,dateadd(day,7,WkEdDate)
			  ,year(dateadd(day,7,Wkdate))
			  ,month(dateadd(day,7,Wkdate))
			  ,CASE DATEPART(QUARTER,dateadd(day,1,Wkdate))
				   WHEN 1 THEN N'Q1'
				   WHEN 2 THEN N'Q2'
				   WHEN 3 THEN N'Q3'
				   ELSE N'Q4' END AS [Quarter]
			  ,cast(CASE DATEPART(W, dateadd(day,1,Wkdate))
				   WHEN 1 THEN N'Monday'
				   WHEN 2 THEN N'Tuesday'
				   WHEN 3 THEN N'Wednesday'
				   WHEN 4 THEN N'Thursday'
				   WHEN 5 THEN N'Friday'
				   WHEN 6 THEN N'Saturday'
				   ELSE N'Sunday' END as varchar(10)) AS [Weekday]
			  ,N'W'+CAST( DATEPART(WEEK, dateadd(day,1,Wkdate)) AS VARCHAR(2)) AS [Week]
		from CTE
        where wkdate < cast(getdate() as date)
    )
    select * from CTE
	Option(MAXRECURSION 7000) --设置最大的递归次数，如果没有这一段，默认最大递归100次