SELECT *
FROM mail_test
ORDER BY MAX(score) OVER (PARTITION BY account_id, `from`) DESC,
         account_id, `from`,
         ROW_NUMBER() OVER (PARTITION BY account_id, `from` ORDER BY score DESC)

fiddle

这可以通过row_number()和max()来实现：

with cte as (
  select *,
           max(score) over (partition by account_id, `from`) as max_score,
           row_number() over (partition by account_id, `from` order by score, id) as rn
  from mail_test
)
select id, account_id, score, `from`
from cte
order by max_score desc, rn desc

这是mysql 5.7的一个工作解决方案：

select mt.*
from `mail_test` mt
inner join (
  select account_id, `from`, max(score) as max_score
  from `mail_test`
  group by account_id, `from`
) as s on s.account_id = mt.account_id and s.`from` = mt.`from`
order by max_score desc, id

结果：

id  account_id  score   from
4   5           4       m@m.com
3   3           3       c@c.com
5   3           1       c@c.com
7   9           3       z@p.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

Demo here

此版本不支持分区：

SELECT m.`id`, m.`account_id`, m.`score`, m.`from`
FROM mail_test m
LEFT JOIN mail_test parent_m
  ON m.account_id = parent_m.account_id
  AND m.from = parent_m.from
  AND m.score < parent_m.score
GROUP BY m.id, m.account_id, m.score, m.from
ORDER BY IFNULL(MAX(parent_m.score), m.`score`) DESC