sql - 如果语句包含 UNION、INTERSECT 或 EXCEPT 运算符(变体)，则 ORDER BY 项必须出现在选择列表中

Question

我已经阅读了我可以在 SO 上找到的与此错误有关的所有问题，但它们并没有完全描述这种情况。在其他人中，人们正在做这样的事情，例如仅按顺序引用一个别名表(从联合的一侧) - 我理解为什么 SQLS 在我读过的所有其他问题中都提示这个特定错误。

我不明白为什么 SQL Server 会遇到这个问题 order by ; order by 中提到的唯一列肯定是结果集 select 的成员:

--example data:
-- a,b,c
-- 1, ,2
--  ,3,5

SELECT    1 AS a, null AS b, 2 AS c INTO #tmp
UNION
SELECT null AS a,    3 AS b, 5 AS c 

--let's call it a lame version of a rollup
SELECT * FROM #tmp            --detail rows
UNION ALL
SELECT a, b, SUM(c) FROM #tmp --summary row
GROUP BY a, b

--the problem
ORDER BY COALESCE(a, b);

DROP TABLE #tmp;

结果集包含列 a和 b ，我可以看到没有歧义..即使对所有内容(不同)进行别名也无济于事:

SELECT t.a AS z, t.b AS y, t.c FROM #tmp t
UNION ALL
SELECT u.a AS z, u.b AS y, SUM(c) AS c FROM #tmp u
GROUP BY u.a, u.b
ORDER BY COALESCE(z, y);

事实上，奇怪的是，SQL Server 似乎提示得更多:

Msg 207, Level 16, State 1, Line 6
Invalid column name 'z'.
Msg 207, Level 16, State 1, Line 6
Invalid column name 'z'.           --why complain twice?
Msg 207, Level 16, State 1, Line 6
Invalid column name 'y'.
Msg 104, Level 16, State 1, Line 6
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

唯一有效的方法是将其包装为另一个选择:

SELECT * FROM(
  SELECT * FROM #tmp
  UNION ALL
  SELECT a, b, SUM(c) AS c FROM #tmp
  GROUP BY u.a, u.b
) a
ORDER BY COALESCE(a, b);

SELECT * FROM(
  SELECT t.a AS z, t.b AS y, t.c FROM #tmp t
  UNION ALL
  SELECT u.a AS z, u.b AS y, SUM(c) AS c FROM #tmp u
  GROUP BY u.a, u.b
) z
ORDER BY COALESCE(z, y);

这就是我认为的，从概念上讲，SQL Server 在处理 order by 之前对其结果集进行了处理。无论如何..那么是什么？

Answer 1

根据 HoneyBadger 的说明，似乎必须真正仅在错误消息面值处采用 SQLS，而不是假设它从联合查询构建结果集，别名为前导选择中的列名称，然后排序..

这个...

SELECT a, b, COALESCE(a,b) FROM t
UNION ALL
SELECT a, b, COALESCE(a,b) FROM u
ORDER BY COALESCE(a,b)

...有效，大概是因为它直接指定了 COALESCE(a,b)在选择列表以及 ORDER BY 中

这个...

SELECT * FROM(
  SELECT a, b FROM t
  UNION ALL
  SELECT a, b FROM u
)z
ORDER BY COALESCE(a,b)

...有效，大概是因为被订购的查询不包含 UNION

使用非工作形式的其他数据库的有趣结果组合:

甲骨文:

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

PostGres:

ERROR: invalid UNION/INTERSECT/EXCEPT ORDER BY clause Detail: Only result column names can be used, not expressions or functions. Hint: Add the expression/function to every SELECT, or move the UNION into a FROM clause

MySQL:

(works)