with cte as (
select *
from (
values(1),(2),(3),(4),(5)
) v (a)
),r as (
select a
from cte
where a in (1,2,3)
union all
select a
from (
select a
from cte
except
select a
from r
) x
)
select a
from r
我试图在Stack Overflow上回答question时遇到了这个问题.
解决方法
解释你所看到的,以及为什么：
我在这里使用表变量,以区分锚值和递归项更清晰(它不会改变语义).
DECLARE @V TABLE (a INTEGER NOT NULL)
INSERT @V (a) VALUES (1),(2)
;
WITH rCTE AS
(
-- Anchor
SELECT
v.a
FROM @V AS v
UNION ALL
-- Recursive
SELECT
x.a
FROM
(
SELECT
v2.a
FROM @V AS v2
EXCEPT
SELECT
r.a
FROM rCTE AS r
) AS x
)
SELECT
r2.a
FROM rCTE AS r2
OPTION (MAXRECURSION 0)
查询计划是：
执行从计划的根目录(SELECT)开始,控制权将树向下传递到索引假脱机,连接,然后传递到顶级表扫描.
扫描的第一行向上传递树,并且(a)存储在堆栈假脱机中,并且(b)返回到客户端.首先没有定义哪一行,但为了参数,我们假设它是值为{1}的行.因此出现的第一行是{1}.
再次控制向下传递到表扫描(连接运算符在打开下一个输入之前从其最外面的输入中消耗所有行).扫描发出第二行(值{2}),这将再次向上传递树以存储在堆栈上并输出到客户端.客户端现在已收到序列{1},{2}.
采用LIFO堆栈顶部位于左侧的约定,堆栈现在包含{2,1}.当控件再次传递给表扫描时,它不再报告行,并且控制传递回连接操作符,这将打开它的第二个输入(它需要一行传递到堆栈线轴),并且控制传递给内部连接首次.
Inner join在其外部输入上调用Table Spool,它从堆栈{2}读取顶行并从工作表中删除它.堆栈现在包含{1}.
在其外部输入上接收到一行后,内部连接将其内部输入控制向左反连接(LASJ).这从其外部输入请求一行,将控制传递给Sort. Sort是一个阻塞迭代器,因此它从表变量读取所有行并对它们进行升序排序(当它发生时).
因此Sort发出的第一行是值{1}. LASJ的内侧返回递归成员的当前值(刚从堆栈中弹出的值),即{2}. LASJ的值为{1}和{2},因此值为{1},因为值不匹配.
此行{1}将查询计划树向上流向索引(堆栈)假脱机,并将其添加到堆栈(现在包含{1,1})并发送到客户端.客户端现已收到序列{1},{2},{1}.
Control现在返回到Concatenation,从内侧返回(它最后一次返回一行,可能再次返回),然后通过Inner Join返回到LASJ.它再次读取其内部输入,从Sort中获取值{2}.
递归成员仍然是{2},所以这次LASJ找到{2}和{2},导致没有发出行.在其内部输入上找不到更多行(Sort现在不在行中),控件将返回到内部连接.
内部连接读取其外部输入,这导致值{1}从堆栈{1,1}弹出,使堆栈仅为{1}.现在重复该过程,来自Table Scan和Sort的新调用的值{2}通过LASJ测试并被添加到堆栈,并传递给客户端,该客户端现已收到{1},{1},{2} ……我们走了.
我最喜欢的递归CTE计划中使用的堆栈线轴explanation是Craig Freedman的.
