SQL Server的公用表表达式CTE递归的学习使用记录

MSDN资料：https://docs.microsoft.com/zh-cn/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

园友资料：https://www.cnblogs.com/CareySon/archive/2011/12/12/2284740.html

 

基本使用

公用表表达式CTE（Common Table Expression），可以定义一个临时命名的结果集，该结果集需要在紧接的下一句的SQL语句中被使用。

语法：

WITH expression_name [ ( column_name [,...n] ) ] 

AS 

( CTE_query_definition ) 

示例：

;WITH T1 AS 
(
    SELECT [UserID],[UserName],[Status],[Age] FROM Student WHERE [Status]='毕业'
)
SELECT COUNT([UserID]) FROM T1 --正常运行
SELECT [UserID],[UserName],[Status],[Age] FROM T1 WHERE [Age]>18 --出错， CTE仅在其定义后紧接的下一条SQL语句中当中有效

 可以语句中使用逗号分隔定义多个CTE

;WITH T1 AS 
(
    SELECT [UserID],[UserName],[Gender] FROM Student
)
, T2 AS 
(
    SELECT [UserID],[UserName] FROM Teacher 
)
, T3 AS 
(
    SELECT [UserID],[UserName] FROM SchoolLeader 
)

 

使用递归公用表表达式做递归查询父级部门

数据准备

查询语句

WITH CTE1 AS 
(
    SELECT
        DepartmentID,
        DepartmentName,
        ParentID,
        CAST(DepartmentID AS varchar(MAX)) AS FullPathID,
        CAST(DepartmentName AS nvarchar(MAX)) AS FullPathName
    FROM Department AS D1
    WHERE (ISNULL(ParentID, '') = '') 
    UNION ALL 
    SELECT
        D2.DepartmentID,
        D2.DepartmentName,
        D2.ParentID,
        C.FullPathID + '>' + D2.DepartmentID AS FullPathID,
        C.FullPathName + '>' + D2.DepartmentName AS FullPathName
    FROM Department AS D2 
    INNER JOIN CTE1 AS C ON C.DepartmentID = D2.ParentID
)

SELECT
    CTE1.DepartmentID,
    CTE1.DepartmentName,
    CTE1.ParentID,
    CTE1.FullPathID,
    CTE1.FullPathName
FROM CTE1 

 语句分析

查询效果

 使用使用 MAXRECURSION 限制递归次数

可以使用 MAXRECURSION 来防止不合理的递归 CTE 进入无限循环，或者限制递归层级以达到某些特定的需求。