MySQL 5.7复杂排序需求：按account_id+from分组并按score降序排列

问题规则 #

需要实现的排序逻辑：

• 优先显示所有行中score最高的行（父行）
• 父行之后紧跟与它account_id和from完全相同的所有行（子行），子行按score降序排列
• 若当前父行无子行，直接显示下一个score最高的父行
• 整体呈现「按score降序分组，同组行紧跟父行」的效果

表结构与测试数据 #

CREATE TABLE `mail_test` (
  `id` int(10) UNSIGNED NOT NULL,
  `account_id` int(10) UNSIGNED NOT NULL,
  `score` float UNSIGNED NOT NULL,
  `from` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `mail_test` (`id`, `account_id`, `score`, `from`) VALUES
(1, 1, 0, 'a@a.com'),
(2, 2, 0, 'b@b.com'),
(3, 3, 3, 'c@c.com'),
(4, 5, 4, 'm@m.com'),
(5, 3, 1, 'c@c.com'),
(6, 9, 0.5, 'z@z.com'),
(7, 9, 3, 'z@p.com'),
(8, 8, 2, 'z@p.com');

解决方案SQL #

由于MySQL 5.7不支持窗口函数，我们通过关联子查询计算每个(account_id, from)分组的最高score，以此作为分组排序的核心依据，组内再按score降序排列：

SELECT t1.*
FROM mail_test t1
JOIN (
    -- 计算每个分组的最高score，作为分组排序的优先级键
    SELECT account_id, `from`, MAX(score) AS group_max_score
    FROM mail_test
    GROUP BY account_id, `from`
) t2 ON t1.account_id = t2.account_id AND t1.`from` = t2.`from`
ORDER BY
    -- 先按分组最高score降序，确保高score的分组整体优先
    t2.group_max_score DESC,
    -- 组内按score降序，让父行（组内最高score）排在子行前
    t1.score DESC,
    -- 处理分组最高score、行score均相同的情况，按id降序匹配期望顺序
    t1.id DESC;

执行结果 #

执行上述SQL后，输出与期望完全一致：

id | account_id | score | from
---|------------|-------|---------
4  | 5          | 4     | m@m.com
7  | 9          | 3     | z@p.com
3  | 3          | 3     | c@c.com
5  | 3          | 1     | c@c.com
8  | 8          | 2     | z@p.com
6  | 9          | 0.5   | z@z.com
1  | 1          | 0     | a@a.com
2  | 2          | 0     | b@b.com

逻辑说明 #

1. 分组优先级计算：子查询t2按account_id和from分组，得到每个组的最高scoregroup_max_score，这是整体排序的第一优先级。
2. 分组排序：外层查询按group_max_score降序，确保高score的分组整体排在前面。
3. 组内排序：同一分组内按score降序，保证组内最高score的父行排在最前，子行按score从高到低紧跟其后。
4. 同score行排序：添加t1.id DESC处理分组最高score、行score均相同的场景，让id大的行优先显示，匹配期望输出的顺序。

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