递归 CTE

引言

对于 T-SQL 的新手来说，递归 CTE 是一个非常难以掌握的概念。即使是经验丰富的开发者，也会觉得它是一个难以攻克的难题并难以应用。

我将尝试用我所能做的最简单的例子来解释递归 CTE 的概念。

背景

下面是我将用于进一步解释的表结构。

递归 CTE 常用于遍历父子层级结构。

如果您查看“员工层级”表，您会注意到一个自引用键 SUPERVISORID，它指向同一个表（因此是自引用），并且它指向的列是 EMPID，而 EMPID 恰好是“员工层级”表的主键。SUPERVISORID，顾名思义，是负责管理该员工的上级的 EMPID。因此，例如，由于 Rocky 的 SUPERVISORID 是 4，Dimitri 是他的老板。Dimitri 的 EMPID 是 4。

如果您坐下来仔细思考一下，您会意识到您遇到了一个“不知道兔子洞有多深”的情况。假设有人要求您从 Rocky 本人开始，一直追溯到最高层级的领导，这个领导没有上级（也许是 CEO？）。在我们的例子中，这个最高层的人是 Aravind。但从 Rocky 本人开始，我只知道他的上级或老板是 EMPID=4 的人，即 Dimitri。现在当我找到 Dimitri 时，我发现他的老板是 EMPID=2 的人，即 Bakshi。就像一个执着的调查员决心要破案一样，我找到了 Bakshi，发现他向上级 EMPID=1 的人汇报，然后就找到了！这个人是 Arvind，他没有汇报对象。在我们的情况下，用了 3 步（Rocky 到 Dimitri，然后 Dimitri 到 Bakshi，最后 Bakshi 到 Aravind）才到达公司食物链的顶端，但您确实意识到您事先不知道需要多少步。您可能需要 30 步，甚至 300 步！

如果有人要求您打印 Rocky 的详细信息以及他老板的详细信息，您可以使用简单的自连接来实现。通俗地说，您可以将 EmployeeHierarchy 表与 EmployeeHierarchy 表连接起来，然后同时输出 Rocky 的详细信息和他老板 Dimitri 的详细信息。请参阅 What is SELF JOIN and when would you use it? 来了解我在说什么。

如果有人要求您打印 Rocky 的详细信息、Dimitri 的详细信息以及 Dimitri 老板的详细信息呢？这只需要一点点逻辑推断，就可以看出您现在需要对 EmployeeHierarchy 表进行 2 次自连接。换句话说，这个表将三次参与连接关系。

一点归纳推理会告诉您，您想深入兔子洞越深（或者说想爬多高的公司梯子），就需要越来越多的自连接。但是，如果您事先不知道需要挖多深呢？这时递归逻辑就派上用场了。

 

那么，让我们开始我们的兔子洞之旅吧。我们有什么可以开始的？我们的铲子在哪里？

SELECT EMPID,
EMPNAME,
SUPERVISORID
FROM dbo.EmployeeHierarchy
WHERE EMPID=8

Rocky 的 EMPID 是 8。他是我们的铲子。我喜欢称他为“种子”。你可以称他为“alpha”。T-SQL 专家喜欢称之为“锚点”。随你怎么称呼。只要记住，他是我们启动事物的钥匙。

现在，我将按行处理。我有一行对应 Rocky。我现在将添加 Rocky 的老板 Dimitri。

我如何**添加** Dimitri？使用 UNION 如何？

SELECT EMPID,
EMPNAME,
SUPERVISORID
FROM dbo.EmployeeHierarchy
WHERE EMPID=8

UNION

SELECT src.EMPID,
src.EMPNAME,
src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN <<some imaginary query which returns (8,Rocky,4) according to a schema (EMPID, EMPNAME, SUPERVISORID)>>
ON src.EMPID=<<the same imaginary query which returns (8,Rocky,4) according to a schema (EMPID, EMPNAME, SUPERVISORID)>>.SUPERVISORID

等等！

<<some imaginary query which returns (8,Rocky,4) according to a schema (EMPID, EMPNAME, SUPERVISORID)>>

我是认真的吗？我说的这些双尖括号里的臃肿内容是什么意思？

双尖括号（<< >>）之间的所有这些臃肿内容是理解递归 CTE 的关键。当我说的“某个假设的查询，根据模式（EMPID, EMPNAME, SUPERVISORID）返回 (8,Rocky,4)”时，我的意思是，现在想象有一个子查询，它返回 3 列——EMPID、EMPNAME 和 SUPERVISORID，并且目前，该子查询只返回一条记录，其中 EMPID=8、EMPNAME=Rocky 和 SUPERVISORID=4。

假设一个子查询？ 

请继续跟着我。

为了方便起见，我们以后就称我们的假设子查询为 ISQ-V1，好吗？简而言之——“假设子查询版本 1”，即 ISQ-V1。

那么内部连接呢？好吧，我将 EmployeeHierarchy 表与我的假设子查询返回的结果集进行连接，连接条件是 EmployeeHierarchy 的 EMPID 和我的假设子查询的 SUPERVISORID。

请仔细看下面的内容。在代码片段右下角，双尖括号的右侧有一个点，后面跟着 SUPERVISORID。这基本上意味着（请原谅我的重复）——

我将 EmployeeHierarchy 表与我的假设子查询返回的结果集进行连接，连接条件是 EmployeeHierarchy 的 EMPID 和我的假设子查询的 SUPERVISORID。

INNER JOIN <<some imaginary query which returns (8,Rocky,4) according to a schema (EMPID, EMPNAME, SUPERVISORID)>>
ON src.EMPID=<<the same imaginary query which returns (8,Rocky,4) according to a schema (EMPID, EMPNAME, SUPERVISORID)>>.SUPERVISORID

现在，这个 UNION 给我留下了什么？为了更好地理解，我们稍微缩短一下上面庞大的语法。我们将使用我们的简写 ISQ-V1 来表示。

SELECT EMPID,
EMPNAME,
SUPERVISORID
FROM dbo.EmployeeHierarchy
WHERE EMPID=8

UNION

SELECT src.EMPID,
src.EMPNAME,
src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN ISQ-V1
ON src.EMPID=ISQ-V1.SUPERVISORID

上面的语句返回了 2 行结果集——

EMPID	EMPNAME	SUPERVISORID
8	Rocky	4
4	Dimitri	2

让我们再稍微扩展一下我们的想象力。假设一个新的子查询返回上面的结果集。称之为 ISQ-V2。

现在，我们将把它提升一个档次！考虑下面的查询。

SELECT EMPID,
EMPNAME,
SUPERVISORID
FROM dbo.EmployeeHierarchy
WHERE EMPID=8

UNION

SELECT src.EMPID,
src.EMPNAME,
src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN ISQ-V1
ON src.EMPID=ISQ-V1.SUPERVISORID

UNION

SELECT src.EMPID,
src.EMPNAME,
src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN ISQ-V2
ON src.EMPID=ISQ-V2.SUPERVISORID

如果您动动脑筋，您会发现上面的查询为您返回——

EMPID	EMPNAME	SUPERVISORID
8	Rocky	4
4	Dimitri	2
2	Bakshi	1

我将再次扮演“归纳侦探”的角色，称这个结果集为假设子查询 ISQ-V3 的结果！

您能猜出下面的查询会给我什么吗？

SELECT EMPID,
EMPNAME,
SUPERVISORID
FROM dbo.EmployeeHierarchy
WHERE EMPID=8

UNION

SELECT src.EMPID,
src.EMPNAME,
src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN ISQ-V1
ON src.EMPID=ISQ-V1.SUPERVISORID

UNION

SELECT src.EMPID,
src.EMPNAME,
src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN ISQ-V2
ON src.EMPID=ISQ-V2.SUPERVISORID

UNION

SELECT src.EMPID,
src.EMPNAME,
src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN ISQ-V3
ON src.EMPID=ISQ-V2.SUPERVISORID

这个！

EMPID	EMPNAME	SUPERVISORID
8	Rocky	4
4	Dimitri	2
2	Bakshi	1
1	Aravind	NULL

如果我现在将上面的结果称为 ISQ-V4，并继续我艰苦的探索以揭示最高老板，会怎么样？

SELECT EMPID
    ,EMPNAME
    ,SUPERVISORID
FROM dbo.EmployeeHierarchy
WHERE EMPID = 8

UNION

SELECT src.EMPID
    ,src.EMPNAME
    ,src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN ISQ - V1 ON src.EMPID = ISQ - V1.SUPERVISORID

UNION

SELECT src.EMPID
    ,src.EMPNAME
    ,src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN ISQ - V2 ON src.EMPID = ISQ - V2.SUPERVISORID

UNION

SELECT src.EMPID
    ,src.EMPNAME
    ,src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN ISQ - V3 ON src.EMPID = ISQ - V3.SUPERVISORID

UNION

SELECT src.EMPID
    ,src.EMPNAME
    ,src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN ISQ - V4 ON src.EMPID = ISQ - V4.SUPERVISORID

我仍然得到了——

EMPID	EMPNAME	SUPERVISORID
8	Rocky	4
4	Dimitri	2
2	Bakshi	1
1	Aravind	NULL

请注意，没有任何新内容被添加。ISQ-V4 的前一个结果集与我上面得到的结果相同。这标志着我艰苦探索的结束。我已经找到了兔子洞的底部！

请注意我们现在手中这个巨大的查询。它有一个模式。如果我们去掉我们的“种子”查询，第一个 UNION 之后的所有内容都基本上是相同的语法，只是 ISQ-V1 升级为 ISQ-V2，然后是 ISQ-V3，依此类推。

还请注意，ISQ-V2 的输出包含了 ISQ-V1 的输出。ISQ-V2“拥有”ISQ-V1。同样地，ISQ-V3“拥有”ISQ-V2，并且传递性地也拥有 ISQ-V1。ISQ-V4 拥有 V3 到 V1 的所有人。ISQ 感觉就像一个不断膨胀的气泡，不是吗？一个气泡，每次迭代都会添加一条新记录。就像每次迭代一样，一个更成熟、更年长、更大的 ISQ 版本从一个不成熟、更年轻、更小的 ISQ 版本的茧中孵化出来。

有没有一种方法可以创建一个自我训练的 ISQ 气泡，它能够自动生长，依靠自身年轻时的表现，根据需要添加记录，并且知道何时停止生长？

叹气！听起来像个梦，不是吗？

是的，这就是递归 CTE。它将所有重复、冗余的 UNION 合并成一个单一的、优雅、紧凑的 UNION ALL，并在其顶部加上一个自引用子句。将自引用想象成气泡引用其先前版本以获取生长所需的养分。

;with [ISQ-bubble] as
(
SELECT EMPID,
EMPNAME,
SUPERVISORID
FROM dbo.EmployeeHierarchy
WHERE EMPID=8

UNION ALL

SELECT src.EMPID,
src.EMPNAME,
src.SUPERVISORID
FROM dbo.EmployeeHierarchy src
INNER JOIN [ISQ-bubble]
ON src.EMPID=[ISQ-bubble].SUPERVISORID
)
SELECT * FROM [ISQ-bubble]
order by empid

这就是递归 CTE。

“UNION ALL”是一个语法约束。您不能将其替换为“UNION”。试试看。您会收到语法错误。您可以看到上面的自引用。在内部，整个过程将像我描述的那样工作，就像一名侦探追随一个线索引出另一个线索一样。

这就引出了另一个问题？侦探可以追随多少条线索？在他筋疲力尽之前（也许）？或者抓狂而放弃？默认情况下，SQL Server 允许递归深度达 100 层。您可以使用 OPTION (MAXRECURSION <<指定您想要的深度>>) 来更改此设置。这里有一个很好的链接 - OPTION MAXRECURSION | SQL with Manoj。

在结束之前，再给您一个不错的技巧和思考题。如果我将递归 CTE 看作一个不断增长、膨胀、自引用的气泡，并且它能自动知道何时停止，您能告诉我这段代码做什么吗？

;WITH recursive_counter
AS (
    SELECT 1 AS seq   

    UNION ALL   

    SELECT recursive_counter.seq + 1
    FROM recursive_counter
    WHERE recursive_counter.seq < 1000
    )
SELECT *
FROM recursive_counter
OPTION (MAXRECURSION 1000)

另外，问问自己，如果我删除过滤条件 'WHERE recursive_counter.seq<1000' 和 OPTION (MAXRECURSION 1000) 会发生什么？如果我删除其中一个，而保留另一个呢？试试看。

附注：在制作此答案的过程中，没有一个自引用、无限膨胀的气泡受到伤害。