UNION/UNION ALL为何打乱查询结果顺序？如何保留排序？

问题原因 #

使用UNION ALL（或UNION）合并多个子查询时，数据库会忽略每个子查询内部的ORDER BY（除非子查询搭配了LIMIT——此时ORDER BY仅用于筛选LIMIT的结果，而非最终整体排序）。合并后的结果集只会按照数据库默认的存储逻辑或优化后的顺序返回，因此原本两个子查询的排序规则会被完全打乱。

解决方案 #

要保留两个子查询各自的排序逻辑，同时保证第一部分结果在前、第二部分在后，需要给不同部分的结果添加分组排序标识，然后在整体查询的末尾统一指定排序规则：

方法一：使用统一内部排序字段 #

SELECT result FROM (
    -- 第一部分：姓名+职业首字母，标记为排序组1，内部按name排序
    SELECT concat(name, substring(occupation, 1, 1)) AS result,
           1 AS sort_group,
           name AS inner_sort
    FROM occupations
    UNION ALL
    -- 第二部分：统计语句，标记为排序组2，内部按数量、职业排序
    SELECT concat('There are a total of ', count(occupation), ' ', occupation, 's.') AS result,
           2 AS sort_group,
           concat(count(occupation), occupation) AS inner_sort
    FROM occupations
    GROUP BY occupation
) AS combined_results
ORDER BY sort_group, inner_sort;

方法二：针对分组指定独立排序字段（更精准） #

如果想更清晰拆分不同分组的排序规则，可以用多字段排序：

SELECT result FROM (
    SELECT concat(name, LEFT(occupation, 1)) AS result,
           1 AS group_order,
           name AS name_sort
    FROM occupations
    UNION ALL
    SELECT concat('There are a total of ', COUNT(*), ' ', occupation, 's.') AS result,
           2 AS group_order,
           COUNT(*) AS count_sort,
           occupation AS occ_sort
    FROM occupations
    GROUP BY occupation
) AS all_results
ORDER BY group_order,
         -- 第一组按姓名排序
         name_sort,
         -- 第二组先按数量，再按职业名称排序
         count_sort, occ_sort;

说明 #

• group_order字段用来确保第一部分结果始终排在第二部分之前；
• 后续的排序字段分别对应两个子查询单独执行时的排序规则，完美还原预期的结果顺序。

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