主页 / dba / 问题 / 324065
Accepted
SlowMagic
Asked: 2023-02-27 01:07:31 +0800 CST

带有 UNION ALL 的 CTE 未按预期工作

  • 772

下面的查询看起来简单明了，但它会产生意想不到的结果。


CREATE TABLE #NUMBERS
(
    N BIGINT
);

INSERT INTO #NUMBERS VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9)
;



WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
),
B AS
(
    SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
    
    UNION ALL

    SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A

    UNION ALL

    SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A
)
SELECT   *
FROM     B
ORDER BY B.QUANTITY
;

我希望它执行一次 CTE A，然后将这些结果带入 CTE B 以产生如下所示的结果：

数量 单元
4个 米
400 厘米
4000 毫米
400万 微米
4000000000 纳米

但是，它会产生如下结果：

数量 单元
8个 米
700 厘米
1000 毫米
600万 微米
3000000000 纳米

这意味着它将返回并执行 CTE A 五次，每次在 CTE B 中提及 A 一次。这不仅是不需要的和不直观的，而且看起来效率也不必要地低。

发生了什么，CTE 天才将如何重写它以产生预期的结果？


顺便说一句，关于 CTE 的 Microsoft 文档页面包含这个可能相关也可能不相关的神秘声明：

如果定义了多个 CTE_query_definition，则查询定义必须由以下集合运算符之一连接：UNION ALL、UNION、EXCEPT 或 INTERSECT。


最后，重写查询以消除 CTE B 并没有帮助：

WITH
A AS
(   
    -- CHOOSE A ROW AT RANDOM
    SELECT   TOP 1 *
    FROM     #NUMBERS            
    ORDER BY NewID()           
)
SELECT   *
FROM     (
          SELECT A.N AS QUANTITY, 'METERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*100 AS QUANTITY, 'CENTIMETERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*1000 AS QUANTITY, 'MILLIMETERS' AS UNIT FROM A
    
          UNION ALL

          SELECT A.N*1000000 AS QUANTITY, 'MICRONS' AS UNIT FROM A

          UNION ALL

          SELECT A.N*1000000000 AS QUANTITY, 'NANOMETERS' AS UNIT FROM A

         ) AS B
ORDER BY B.QUANTITY
;
sql-server-2017

3 个回答

  1. Best Answer

    将公用表表达式更像是表达式而不是（永久）表是有帮助的。每次引用公用表表达式时，它都必须重新表达自己。

    这是一个简单的例子：

    DECLARE
    @t table(id int);

INSERT 
    @t
(
    id
)
SELECT
    id = 1

SET STATISTICS XML ON;

WITH
    t AS
(
    SELECT
        t.id
    FROM @t AS t
)
SELECT
    t.*
FROM t 
JOIN t AS t1
  ON t1.id = t.id
JOIN t AS t2
  ON t2.id = t.id;

    查询计划看起来像这样，对于公共表表达式之间的每个连接都有一个到基表变量的连接：

    坚果

    同样， UNION (ALL) 每次也会产生一个引用：

    WITH
    t AS
(
    SELECT
        t.id
    FROM @t AS t
)
SELECT
    t.*
FROM t 
UNION ALL
SELECT
    t.*
FROM @t AS t
UNION ALL
SELECT
    t.*
FROM @t AS t;

    坚果

    如果您需要稳定结果，则需要使用：

    • #temp桌子
    • @table多变的
    • 永久表
    • 7

  2. 其他答案已经解释了问题发生的原因：基本上，CTE 只是一个表达式，它的计算次数与它被引用的次数一样多，因此导致A在每次计算时返回不同的值。

    我想在我的回答中解决的是问题的这一部分：

    CTE 天才如何重写它以产生预期的结果？

    在 CTE 天才聚集在一起讨论他们的 CTE 相关业务的一些地方闲逛可能教会了我一些我想分享的技巧。

    我认为在这里解决手头的问题非常有用的是两件事：

    • 营办商CROSS APPLY；
    • 行构造函数VALUES。

    使用这两个，我会B像这样专门重写 CTE：

    B AS
(
    SELECT   X.*
    FROM     A
    CROSS APPLY
    (
        VALUES
        (A.N, 'METERS'),
        (A.N*100, 'CENTIMETERS'),
        (A.N*1000, 'MILLIMETERS'),
        (A.N*1000000, 'MICRONS'),
        (A.N*1000000000, 'NANOMETERS')
    ) AS X (QUANTITY, UNIT)
)

    保留查询的其余部分不变。

    方法B在上面定义，A仅被引用（和评估）一次。它仍然生成行集而不是单个行，因为它用行集替换（在 的帮助下CROSS APPLY）返回的行A，并且行集（由 构造VALUES）基本上作为A.N参数，生成所需的值集。

    您可以在dbfiddle.uk上测试完整的查询。

    • 7

  3. CTE 并不总是像许多人认为的那样具体化。

    • 1

相关问题