sql-server - 为什么 Transact-SQL 中的递归 CTE 需要 UNION ALL 而不是 UNION？

Question

我知道 anchor 是必要的，这是有道理的。我知道需要一个 UNION ALL ，如果你的递归 CTE 没有，它就不起作用......但我找不到一个很好的解释为什么这是案件。所有文档只是说明您需要它。

为什么我们不能在递归查询中使用 UNION 而不是 UNION ALL ？在更深层次的递归中不包含重复项似乎是个好主意，不是吗？我认为类似的东西应该已经在幕后工作了。

Answer 1

我认为原因是他们没有认为这是一个值得实现的优先功能。看起来像 Postgres does support both UNION 和 UNION ALL。

如果您对此功能有充分的理由，可以通过 Connect 提供反馈(或者无论其替换的 URL 是什么)。

防止添加重复项可能很有用，因为在后续步骤中添加到前一步的重复行几乎总是会导致无限循环或超出最大递归限制。

SQL Standards中有不少地方其中使用代码演示UNION，如下所示

本文解释了如何 they are implemented in SQL Server 。他们并没有“在幕后”做类似的事情。堆栈假脱机会在行进行时删除行，因此不可能知道后面的行是否与已删除的行重复。支持 UNION 需要采用稍微不同的方法。

与此同时，您可以在多语句 TVF 中轻松实现相同的目标。

下面举一个愚蠢的例子( Postgres Fiddle )

WITH R
     AS (SELECT 0 AS N
         UNION
         SELECT ( N + 1 )%10
         FROM   R)
SELECT N
FROM   R 

将 UNION 更改为 UNION ALL 并在末尾添加 DISTINCT 不会使您免于无限递归。

但是你可以将其实现为

CREATE FUNCTION dbo.F ()
RETURNS @R TABLE(n INT PRIMARY KEY WITH (IGNORE_DUP_KEY = ON))
AS
  BEGIN
      INSERT INTO @R
      VALUES      (0); --anchor

      WHILE @@ROWCOUNT > 0
        BEGIN
            INSERT INTO @R
            SELECT ( N + 1 )%10
            FROM   @R
        END

      RETURN
  END

GO

SELECT *
FROM   dbo.F () 

上面使用IGNORE_DUP_KEY来丢弃重复项。如果列列表太宽而无法建立索引，则需要使用 DISTINCT 和 NOT EXISTS 来代替。您可能还需要一个参数来设置最大递归数并避免无限循环。