PostgreSQL技术需求：按name分组查询每组最低的3个score记录

问题场景 #

现有results表，结构包含id(int)、name(varchar)、score(int)，表数据如下：

id   name   score
1    x      5
2    x      9
3    x      10
5    x      2
85   y      20
2    y      1
9    z      98
2    z      6
7    z      93
10   z      9

需求：按name分组，查询每组中score最低的3条记录，期望输出：

id   name   score
1    x      5
2    x      9
5    x      2
85   y      20
2    y      1
2    z      6
7    z      93
10   z      9

原SQL的问题 #

你尝试的SQL存在两个核心问题：

1. GROUP BY用法错误：使用GROUP BY name时，SELECT列表中的id和score没有聚合函数（如MIN/MAX），PostgreSQL不允许这种非标准写法，会导致语法错误或返回不可预期的结果。
2. 全局LIMIT不符合需求：LIMIT 3是限制整个查询返回3条记录，而非每组返回3条，完全无法实现分组取数的目标。

正确解决方案 #

使用PostgreSQL的窗口函数可以轻松实现分组内的TopN查询，这里推荐两种方式：

方式1：使用ROW_NUMBER()（严格取每组前3条，同score会按行号区分） #

SELECT id, name, score
FROM (
    SELECT 
        id, 
        name, 
        score,
        -- 按name分区，每个分区内按score升序编号
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY score ASC) AS row_num
    FROM results
) ranked_results
-- 筛选每个分区内编号前3的记录
WHERE row_num <= 3
-- 按name和score排序，和期望输出对齐
ORDER BY name, score;

方式2：使用RANK()（保留同score的并列记录） #

如果存在相同score的记录，希望所有并列的最低分都被保留（比如某组有4条记录score相同且都是最低，会全部返回），可以用RANK()替代ROW_NUMBER()：

SELECT id, name, score
FROM (
    SELECT 
        id, 
        name, 
        score,
        RANK() OVER (PARTITION BY name ORDER BY score ASC) AS rank_num
    FROM results
) ranked_results
WHERE rank_num <= 3
ORDER BY name, score;

结果验证 #

执行上述SQL后，会得到与你期望完全一致的输出：

• x组返回score最低的3条：2（id5）、5（id1）、9（id2）
• y组仅有2条记录，全部返回
• z组返回score最低的3条：6（id2）、9（id10）、93（id7）

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