PostgreSQL按唯一ID查询每个ID对应的前5条高分记录求助

看起来你需要从视图xyz中按ID分组，提取每个ID对应的前5条高分记录，之前尝试MySQL方案没成功，大概率是没用到适合分组取Top N的窗口函数或者变量方法，下面给你两种可行的解决方案：

方法一：使用窗口函数（MySQL 8.0+ 支持） #

MySQL 8.0及以上版本支持窗口函数，这是最简洁高效的方式。我们可以用ROW_NUMBER()给每个ID分组内的记录按分数降序编号，然后筛选编号≤5的记录：

SELECT ID, NAME, `......Other Data...`, Marks
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Marks DESC) AS row_num
    FROM xyz
) AS ranked_data
WHERE row_num <= 5;

说明： #

• PARTITION BY ID：将数据按ID分组，每个ID单独处理
• ORDER BY Marks DESC：每个分组内按分数从高到低排序
• ROW_NUMBER()：给每个分组内的记录分配唯一行号，分数相同的行也会得到不同的行号（如果需要保留所有相同高分的记录，比如多个100都要纳入前5，可以换成RANK()或DENSE_RANK()，不过看你的期望结果，ROW_NUMBER()刚好符合需求）

方法二：使用变量（MySQL 5.x 兼容） #

如果你的MySQL版本低于8.0，不支持窗口函数，可以用用户变量来实现分组排序和编号：

SELECT ID, NAME, `......Other Data...`, Marks
FROM (
    SELECT 
        *,
        @row_count := CASE WHEN @current_id = ID THEN @row_count + 1 ELSE 1 END AS row_num,
        @current_id := ID
    FROM xyz, (SELECT @row_count := 0, @current_id := NULL) AS init_vars
    ORDER BY ID, Marks DESC
) AS ranked_data
WHERE row_num <= 5;

说明： #

• 先初始化两个变量@row_count（记录当前分组的行号）和@current_id（记录当前处理的ID）
• 按ID和分数降序排序后，每一行判断是否和上一行ID相同：相同则行号加1，不同则重置行号为1
• 最后筛选行号≤5的记录，得到每个ID的前5条高分数据

为什么之前的方案可能失败？ #

如果之前尝试用LIMIT 5，它只会返回全局前5条，而不是每个ID的前5条；如果用GROUP BY ID，又无法直接保留多条高分记录，所以必须用分组排序编号的方式来实现。

内容的提问来源于stack exchange，提问作者J. Doe