SQL学习求助：MySQL/MariaDB如何实现两列排序的单用户Top5排名

你现有语句仅完成了全量数据排序，没有实现「单用户仅保留最高成绩」的去重逻辑，也没有生成排名序号，可按你使用的数据库版本选择对应写法：

• 如果你使用的是 MySQL 8.0及以上 / MariaDB 10.2及以上版本（支持窗口函数，是目前主流部署版本），直接用窗口函数实现逻辑最清晰，性能也更好：

SELECT
  ROW_NUMBER() OVER(ORDER BY ID_NIVELL DESC, PUNTUACIO DESC) AS rank_num,
  ID_NIVELL,
  user_nick, -- 请替换为你表中实际存储用户昵称的字段名
  PUNTUACIO
FROM (
  SELECT
    *,
    ROW_NUMBER() OVER(
      PARTITION BY user_nick -- 按用户昵称分组，同一用户的记录分到同一分区
      ORDER BY PUNTUACIO DESC, ID_NIVELL DESC -- 同个用户下按分数、关卡ID降序排，最高成绩排第1
    ) AS rn
  FROM partides
  WHERE PUNTUACIO > 0
) t
WHERE rn = 1 -- 仅保留每个用户分区内排名第1的最高成绩记录
ORDER BY ID_NIVELL DESC, PUNTUACIO DESC
LIMIT 5; -- 截取最终Top5榜单

• 如果你使用的是MySQL 5.x等不支持窗口函数的老版本，用用户自定义变量+分组聚合的兼容写法：

SELECT
  @rank := @rank + 1 AS rank_num,
  p.ID_NIVELL,
  p.user_nick,
  p.PUNTUACIO
FROM partides p
INNER JOIN (
  -- 先聚合查出每个用户的最高分数
  SELECT user_nick, MAX(PUNTUACIO) AS max_puntuacio
  FROM partides
  WHERE PUNTUACIO > 0
  GROUP BY user_nick
) tmp ON p.user_nick = tmp.user_nick AND p.PUNTUACIO = tmp.max_puntuacio,
(SELECT @rank := 0) r -- 初始化排名计算变量
GROUP BY p.user_nick -- 避免同用户出现相同最高分导致重复记录
ORDER BY p.ID_NIVELL DESC, p.PUNTUACIO DESC
LIMIT 5;

注意：上述代码中的user_nick为占位符，请替换为你partides表中实际存储用户昵称的字段名再执行。如果同一用户存在多条分数完全相同的最高记录，窗口函数写法会自动优先取关卡ID更高的记录，老版本写法如果需要严格匹配最高关卡ID的记录，可在关联子查询中补充MAX(ID_NIVELL)的关联条件即可。

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