MySQL 5.7中GROUP BY丢失子查询ORDER BY排序的问题如何解决？

这个场景我之前处理过！MySQL 5.7对GROUP BY的行为做了严格规范，开启了默认的ONLY_FULL_GROUP_BY模式，旧版本依赖「子查询排序后分组取首行」的写法确实会失效，因为分组后的结果不再保留子查询的排序顺序。下面给你几个靠谱的兼容方案，都能得到你想要的id=2和3的结果：

方案1：GROUP_CONCAT + SUBSTRING_INDEX（MySQL 5.7兼容） #

利用聚合函数拼接并截取优先级最高的id，再关联原表获取完整行：

SELECT x.*
FROM x
JOIN (
    SELECT 
        externalid, 
        -- 按优先级排序id并拼接，取第一个id
        SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY FIELD(lang, 'fr') DESC, id), ',', 1) AS top_id
    FROM x
    GROUP BY externalid
) AS t ON x.id = t.top_id;

原理：GROUP_CONCAT会把同一个externalid下的id按「是否是fr语言→id升序」的规则拼接成字符串，SUBSTRING_INDEX截取第一个元素就是我们要的优先级最高的行的id，最后关联原表拿到整行数据。

方案2：用户变量模拟窗口函数（MySQL 5.7兼容） #

用变量跟踪当前分组并给行编号，取每组的第一行：

SELECT id, lang, externalid
FROM (
    SELECT *,
           -- 当externalid变化时重置行号，否则递增
           @row_num := IF(@current_ext = externalid, @row_num + 1, 1) AS rn,
           @current_ext := externalid
    FROM x
    -- 初始化变量
    CROSS JOIN (SELECT @current_ext := '', @row_num := 0) AS vars
    -- 先按externalid分组，再按优先级排序
    ORDER BY externalid, FIELD(lang, 'fr') DESC, id
) AS ranked
-- 取每组的第一行
WHERE rn = 1;

原理：通过用户变量@current_ext跟踪当前处理的externalid，@row_num给每个分组内的行按优先级编号，最后筛选出编号为1的行。

方案3：窗口函数ROW_NUMBER（MySQL 8.0+推荐） #

如果你的MySQL版本已经升级到8.0及以上，直接用窗口函数是最简洁的写法：

SELECT id, lang, externalid
FROM (
    SELECT *,
           -- 按externalid分组，按优先级排序并编号
           ROW_NUMBER() OVER (PARTITION BY externalid ORDER BY FIELD(lang, 'fr') DESC, id) AS rn
    FROM x
) AS ranked
WHERE rn = 1;

原理：PARTITION BY externalid实现分组，ORDER BY指定行的优先级，ROW_NUMBER()给每组内的行按顺序编号，取编号1的行就是我们需要的优先行。

为什么旧写法失效？ #

MySQL 5.7默认开启了ONLY_FULL_GROUP_BY SQL模式，这个模式要求SELECT列表中的非聚合列必须出现在GROUP BY子句中，同时不再保证GROUP BY会保留子查询的排序结果，所以你之前的「子查询排序后分组」的写法就无法得到预期结果了。

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