SQL新手求助：如何按年月选取每月分数最高的ID？

嘿，作为SQL新手能先想到获取最高分已经迈出很好的一步啦！我来帮你完成完整的查询，完美匹配你想要的结果～

首先明确你的核心需求：

• 基于给定表数据，按**年月（必须包含年份）**分组
• 找出每个月里分数最高的所有ID（允许多个ID同月份同最高分的情况）

你的表结构与数据 #

方案一：使用窗口函数（推荐，简洁高效） #

现在大多数主流数据库（MySQL 8.0+、PostgreSQL、SQL Server等）都支持窗口函数，用DENSE_RANK()可以轻松实现需求：

SELECT id, mark
FROM (
    SELECT 
        id, 
        mark,
        -- 按年月分组，组内按分数降序排名，最高分排第1
        DENSE_RANK() OVER (PARTITION BY DATE_FORMAT(datetimes, '%Y-%m') ORDER BY mark DESC) AS rank_num
    FROM your_table_name -- 替换成你的实际表名
) AS ranked_data
WHERE rank_num = 1;

代码细节解释：

• DATE_FORMAT(datetimes, '%Y-%m')：把日期转换成YYYY-MM的年月格式，用来按年月分组（不同数据库写法略有差异，下面会补充）
• PARTITION BY ...：将数据按年月拆分成独立的分组
• ORDER BY mark DESC：在每个年月分组内，按分数从高到低排序
• DENSE_RANK()：给每个分组内的记录排名，所有最高分的记录都会被标记为1（即使有多个ID分数相同且都是当月最高）
• 外层查询筛选出排名为1的记录，就是每个月分数最高的所有ID

不同数据库的年月转换写法 #

如果你的数据库不是MySQL，调整年月转换的函数即可：

• PostgreSQL：TO_CHAR(datetimes, 'YYYY-MM')
• SQL Server：FORMAT(datetimes, 'yyyy-MM') 或者 CONVERT(varchar(7), datetimes, 120)
• Oracle：TO_CHAR(datetimes, 'YYYY-MM')

方案二：使用子查询（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用子查询的方式实现：

SELECT t.id, t.mark
FROM your_table_name t
WHERE t.mark = (
    -- 子查询获取当前记录所在年月的最高分
    SELECT MAX(mark)
    FROM your_table_name
    WHERE DATE_FORMAT(datetimes, '%Y-%m') = DATE_FORMAT(t.datetimes, '%Y-%m')
);

代码细节解释：

• 对于表中的每一条记录，子查询会找出它所在年月的最高分
• 外层筛选出分数等于该年月最高分的记录，这样就能得到每个月所有最高分的ID

最终验证结果 #

执行上面任意一种查询后，都会得到你期望的输出：

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