MySQL UNION子句排序异常求助：合并后首查询排序失效

问题情况 #

• 单独执行两个查询均符合预期：
  1. 第一个查询筛选成绩等级≥8的学生，结果按grade降序排列，grade相同时按name升序排列；
  2. 第二个查询筛选成绩等级≤7的学生，name字段置为null，结果按marks升序排列。
• 使用UNION合并两个查询后，第一个查询的排序规则失效，第二个查询排序正常，修改为嵌套子句也无法解决问题。

原SQL代码：

(SELECT s1.name
        , g1.grade
        , s1.marks
FROM students s1
JOIN grades g1 ON s1.MARKS > g1.min_mark 
                 AND s1.MARKS < g1.max_mark
WHERE g1.grade > 7
ORDER BY grade DESC, name ASC)
UNION
(SELECT   s3.name
        , g2.grade
        , s2.marks
FROM students s2
LEFT JOIN grades g2 ON s2.MARKS > g2.min_mark 
                AND s2.MARKS < g2.max_mark
LEFT JOIN (SELECT * FROM students WHERE marks > 69) s3 ON s2.id = s3.id
WHERE g2.grade < 7
ORDER BY s2.marks)

原因分析 #

MySQL的UNION操作会忽略子查询中的ORDER BY，除非子查询搭配了LIMIT子句。因为优化器会判定：没有LIMIT的子查询排序是无意义的，执行时会直接跳过排序步骤，所以单独执行第一个查询时排序有效，合并后排序规则就失效了。

解决方案 #

方案1：给子查询添加LIMIT（保留子查询排序） #

给两个子查询都加上LIMIT，用极大值确保返回所有符合条件的记录：

(SELECT s1.name
        , g1.grade
        , s1.marks
FROM students s1
JOIN grades g1 ON s1.MARKS > g1.min_mark 
                 AND s1.MARKS < g1.max_mark
WHERE g1.grade > 7
ORDER BY grade DESC, name ASC
LIMIT 18446744073709551615) -- 最大无符号整数，确保返回所有结果
UNION
(SELECT NULL AS name -- 直接置为NULL，无需关联额外表
        , g2.grade
        , s2.marks
FROM students s2
JOIN grades g2 ON s2.MARKS > g2.min_mark 
                AND s2.MARKS < g2.max_mark
WHERE g2.grade < 7
ORDER BY s2.marks
LIMIT 18446744073709551615)

方案2：外层统一排序（更清晰可控） #

给两个子查询添加标识字段，在外层统一控制排序规则：

SELECT name, grade, marks FROM (
    SELECT s1.name
            , g1.grade
            , s1.marks
            , 1 AS sort_flag -- 标识第一部分，优先排在前面
    FROM students s1
    JOIN grades g1 ON s1.MARKS > g1.min_mark 
                     AND s1.MARKS < g1.max_mark
    WHERE g1.grade > 7
    UNION
    SELECT NULL AS name
            , g2.grade
            , s2.marks
            , 2 AS sort_flag -- 标识第二部分，排在后面
    FROM students s2
    JOIN grades g2 ON s2.MARKS > g2.min_mark 
                    AND s2.MARKS < g2.max_mark
    WHERE g2.grade < 7
) AS combined
ORDER BY 
    sort_flag, -- 先区分两部分的先后顺序
    CASE WHEN sort_flag = 1 THEN grade END DESC, -- 第一部分按grade降序
    CASE WHEN sort_flag = 1 THEN name END ASC, -- 第一部分grade相同时按name升序
    CASE WHEN sort_flag = 2 THEN marks END ASC; -- 第二部分按marks升序

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