SQL多字段排序实现scorelist表各用户最后赛季得分查询

错误原因分析 #

• 第一段SQL问题：子查询SELECT season FROM scorelist ORDER BY season DESC LIMIT 1取的是全表最大的赛季值3，WHERE条件直接筛选所有赛季为3的记录，自然会过滤掉所有未参与赛季3的用户。
• 第二段SQL问题：直接按user_id分组不符合SQL规范（仅MySQL关闭ONLY_FULL_GROUP_BY校验时允许执行），非分组字段的取值是数据库随机返回的行，不会自动匹配最大赛季对应的记录，结果自然不符合预期。

可行解法 #

解法1：窗口函数（适用于MySQL 8.0+、PostgreSQL、Oracle等支持窗口函数的数据库） #

用ROW_NUMBER按用户分组，给每个用户的赛季倒序编号，取编号为1的就是该用户最后参与赛季的记录：

SELECT ID, USER_ID, SCORE, SEASON 
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY SEASON DESC) AS rn
    FROM scorelist
) t
WHERE rn = 1
ORDER BY SCORE DESC;

执行结果完全匹配预期输出。

解法2：子查询关联（兼容低版本MySQL） #

先查询每个用户的最大赛季，再关联回原表取对应记录：

SELECT s.* 
FROM scorelist s
INNER JOIN (
    SELECT USER_ID, MAX(SEASON) AS max_season
    FROM scorelist
    GROUP BY USER_ID
) t ON s.USER_ID = t.USER_ID AND s.SEASON = t.max_season
ORDER BY s.SCORE DESC;

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