MySQL 5.7如何按分组获取每组前5条最高积分记录？

解决方案 #

在MySQL 5.7中，可以通过用户变量模拟窗口函数的行号功能，实现按年份分组取每组前5条最高积分记录的需求，具体SQL如下：

SELECT id, user_id, date, points, year
FROM (
    SELECT 
        id,
        user_id,
        date,
        points,
        DATE_FORMAT(date,'%Y') AS year,
        -- 用变量生成组内行号：年份变化时重置行号，否则递增
        @row := CASE 
            WHEN @current_year = DATE_FORMAT(date,'%Y') THEN @row + 1
            ELSE 1
        END AS row_num,
        @current_year := DATE_FORMAT(date,'%Y') AS dummy
    FROM 
        your_table_name, -- 替换成你的实际表名
        (SELECT @row := 0, @current_year := '') AS init_vars
    WHERE 
        user_id = 2
    ORDER BY 
        DATE_FORMAT(date,'%Y') ASC, -- 先按年份分组
        points DESC, id DESC -- 组内按积分降序，积分相同则按id降序（保证排序稳定）
) AS ranked_data
WHERE row_num <= 5; -- 筛选每组前5条

关键逻辑说明 #

• 变量初始化：通过(SELECT @row := 0, @current_year := '') AS init_vars初始化两个变量，@row用于记录组内行号，@current_year用于跟踪当前处理的年份。
• 行号生成：在CASE语句中判断当前记录的年份是否和上一条相同，相同则行号+1，不同则重置为1，实现分组内的连续编号。
• 排序规则：内层查询必须先按年份排序，再按积分降序排序，确保行号是在正确的分组内按积分高低生成的。
• 筛选结果：外层查询直接过滤行号≤5的记录，得到每个年份的前5条最高积分记录。

注意事项 #

• 替换SQL中的your_table_name为你的实际表名。
• 若存在多条积分相同的记录，id DESC可以保证排序的稳定性，避免重复或遗漏记录，你也可以根据实际需求调整这部分排序规则。

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