为什么Transact-SQL中的递归CTE需要UNION ALL而不是UNION？ [英] Why does a Recursive CTE in Transact-SQL require a UNION ALL and not a UNION?

问题描述

我认为锚是必要的，这是有道理的。而且我知道需要 UNION ALL ，如果您的递归CTE没有一个，那根本就行不通...但是我找不到一个好的解释为什么会这样。所有文档仅说明您需要它。

为什么不能，我们使用 UNION 代替 UNION ALL ？似乎在深度递归时不包括重复项是一个好主意，不是吗？我想这样的事情应该已经可以在后台进行了。

解决方案

我认为原因是他们只是避风港认为这不是值得实施的优先功能。看起来

本文介绍了它们在SQL Server中实现。他们并没有在幕后做类似的事情。堆栈假脱机会不断删除行，因此无法知道下一行是否是已删除行的重复。支持 UNION 会需要一些不同的方法。

与此同时，您可以轻松地在多语句TVF。

在下面举一个愚蠢的示例（ Postgres Fiddle ）

 有$ R 
 AS（选择0 AS N 
 UNION 
选择（N + 1）％10 
从R）
选择N 
从R 
  

将 UNION 更改为 UNION ALL 并添加 DISTINCT 最后不会使您摆脱无限递归。

但是您可以将其实现为

 创建函数dbo.F（）
返回@R表（n INT PRIMEARY KEY WITH（IGNORE_DUP_KEY = ON））
 AS 
开始
插入@R 
值（0）; --anchor 
 
 @@ ROWCOUNT> 0 
开始
插入@R 
选择（N + 1）％10 
 FROM @R 
 END 
 
返回
 END 
 
 GO 
 
选择* 
从dbo.F（）
  

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

I get that an anchor is necessary, that makes sense. And I know that a UNION ALL is needed, if your recursive CTE doesn't have one, it just doesn't work... but I can't find a good explanation of why that is the case. All the documentation just states that you need it.

Why can't we use a UNION instead of a UNION ALL in a recursive query? It seems like it would be a good idea to not include duplicates upon deeper recursion, doesn't it? Something like that should already be working under the hood already, I would think.

解决方案

I presume the reason is that they just haven't considered this a priority feature worth implementing. It looks like Postgres does support both UNION and UNION ALL.

If you have a strong case for this feature you can provide feedback at Connect (or whatever the URL of its replacement will be).

Preventing duplicates being added could be useful as a duplicate row added in a later step to a previous one will nearly always end up causing an infinite loop or exceeding the max recursion limit.

There are quite a few places in the SQL Standards where code is used demonstrating UNION such as below

This article explains how they are implemented in SQL Server. They aren't doing anything like that "under the hood". The stack spool deletes rows as it goes so it wouldn't be possible to know if a later row is a duplicate of a deleted one. Supporting UNION would need a somewhat different approach.

In the meantime you can quite easily achieve the same in a multi statement TVF.

To take a silly example below (Postgres Fiddle)

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

Changing the UNION to UNION ALL and adding a DISTINCT at the end won't save you from the infinite recursion.

But you can implement this as

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 () 

The above uses IGNORE_DUP_KEY to discard duplicates. If the column list is too wide to be indexed you would need DISTINCT and NOT EXISTS instead. You'd also probably want a parameter to set the max number of recursions and avoid infinite loops.

这篇关于为什么Transact-SQL中的递归CTE需要UNION ALL而不是UNION？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！