如果语句包含 UNION、INTERSECT 或 EXCEPT 运算符(变体)，则 ORDER BY 项必须出现在选择列表中 [英] ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator (variation)
问题描述
我已经阅读了我可以在 SO 上找到的所有与此错误相关的问题，但它们并没有完全描述这种情况.在其他人中，人们正在做这样的事情，例如按顺序仅引用一个别名表(从联合的一侧) - 我理解为什么 SQLS 在我读过的所有其他问题中都抱怨这个特定错误.
I've read all the questions I can find on SO pertaining to this error, and they don't quite describe this situation. In those others, people are doing things like referencing only one of the aliased tables (from one side of the union) in the order by - I understand why SQLS complains in all the other questions I've read, about this particular error.
我不明白为什么 SQL Server 对这个 order by 有问题；order by 中提到的唯一列肯定是结果集 select 的成员:
I can't see why SQL Server is taking issue with this order by; the only columns mentioned in the order by are definitely members of the result set 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，我看不出任何歧义..即使对所有内容(以不同方式)设置别名也无济于事:
The result set contains column a and b, there's no ambiguity that I can see.. Even aliasing everything (differently) doesn't help:
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 似乎抱怨得更多:
In fact, bizarrely, SQL Server seems to complain even more:
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.
唯一有效的方法是将其包装为另一个选择:
The only thing that does work is wrapping it up as another select:
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 之前对其结果集做了什么......那么是什么给出的?
Which is what I thought, conceptually, SQL Server was doing with its result set before it processed the order by anyway.. So what gives?
推荐答案
根据 HoneyBadger 的说明，似乎人们必须真正仅根据错误消息的面值采用 SQLS，而不是假定它从联合查询构建结果集, 别名为前导选择中列的名称，然后排序..
As per note from HoneyBadger, it seems one must really just take SQLS at the error message face value and not assume that it builds a result set from the union query, aliased with the names given to columns in the leading select, that is then ordered..
这...
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
...works, presumably because it directly specifies COALESCE(a,b) in the select list as well as the ORDER BY
这...
SELECT * FROM(
SELECT a, b FROM t
UNION ALL
SELECT a, b FROM u
)z
ORDER BY COALESCE(a,b)
...有效，大概是因为被订购的查询不包含 UNION
...works, presumably because the query being ordered doesn't contain a UNION
使用非工作形式的其他数据库的有趣结果组合:
Interesting mix of results from other DBs using the non-working form:
甲骨文:
ORA-01785:ORDER BY 项必须是 SELECT 列表表达式的编号
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
PostGres:
错误:无效的 UNION/INTERSECT/EXCEPT ORDER BY 子句详细信息:只能使用结果列名称，不能使用表达式或函数.提示:将表达式/函数添加到每个 SELECT，或将 UNION 移动到 FROM 子句中
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:
(作品)
这篇关于如果语句包含 UNION、INTERSECT 或 EXCEPT 运算符(变体)，则 ORDER BY 项必须出现在选择列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
