UNION语句中ORDER BY无法使用别名line的问题咨询

为什么ORDER BY找不到line列？ #

UNION合并两个查询结果时，最终结果集的列名由第一个SELECT语句的列定义决定，第二个SELECT的别名不会覆盖这个规则。你的第一个SELECT语句没有给列指定别名，所以UNION后的结果集里根本不存在名为line的列，自然在ORDER BY里引用会报错。

单独执行第二个SELECT时，别名line是当前查询的列名，所以能正常用于ORDER BY，这和UNION的场景完全不同。

为什么ORDER BY Count(occupation)无效？ #

Count(occupation)是第二个SELECT里的聚合计算逻辑，UNION后的结果集是把两个查询的行合并在一起——第一个查询的行没有这个聚合值，第二个查询的行也只是把聚合值拼到了字符串里，结果集里并没有单独的Count(occupation)列，所以直接引用这个聚合函数排序是不生效的。

解决办法 #

办法1：给第一个SELECT的列也指定别名line #

让UNION后的结果集统一使用line作为列名，这样就能直接ORDER BY了：

Select CONCAT(Name, '(', LEFT(occupation,1), ')') as line
FROM occupations
UNION
Select CONCAT('There are a total of ', Count(Occupation), ' ', occupation, 's') as line
FROM occupations
Group by occupation
ORDER by line

办法2：用子查询包裹UNION结果，再排序 #

如果不想修改第一个SELECT的别名，也可以把UNION的结果作为子查询，在外层按列的位置排序：

SELECT * FROM (
    Select CONCAT(Name, '(', LEFT(occupation,1), ')')
    FROM occupations
    UNION
    Select CONCAT('There are a total of ', Count(Occupation), ' ', occupation, 's')
    FROM occupations
    Group by occupation
) AS temp
ORDER BY 1; -- 1代表结果集的第一列

如果你想按职业的统计数量排序 #

如果你的需求是让统计行按数量排序，同时普通行排在前面，可以把统计数量单独作为一列保留，再排序：

SELECT display_text FROM (
    -- 普通行，统计数量设为0，排序时优先排在前面
    Select CONCAT(Name, '(', LEFT(occupation,1), ')') as display_text, 0 as sort_num
    FROM occupations
    UNION ALL -- 用UNION ALL更高效，因为统计行不会和普通行重复
    -- 统计行，保留统计数量用于排序
    Select CONCAT('There are a total of ', Count(Occupation), ' ', occupation, 's') as display_text, Count(Occupation) as sort_num
    FROM occupations
    Group by occupation
) AS temp
ORDER BY sort_num, display_text;

内容的提问来源于stack exchange，提问作者Abinash Panda