下面的查询看起来简单明了，但它会产生意想不到的结果。
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
;
