我已经阅读了我能找到的与此错误相关的所有问题,但它们并没有完全描述这种情况。在其他情况下,人们正在做一些事情,例如按顺序仅引用一个别名表(从联合的一侧) - 我理解为什么 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
and 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
无论如何..那么什么给呢?