PostgreSQL中如何对rankings表使用带limit限制的rank排名函数

基础表数据 #

现有rankings表全量查询语句及结果如下：

SELECT * FROM rankings;

students | stu_id | card_id
----------+--------+---------
 Charlie  |     11 |       1
 Shakira  |     22 |       2
 Selena   |     33 |       3
 Ross     |     44 |       4
 Chandler |     55 |       5
 James    |     66 |       6
 Kylie    |     77 |       7

实现方法 #

RANK()是PostgreSQL提供的窗口函数，可基于指定排序规则生成排名：相同排序值的记录排名一致，后续排名会跳过重复占有的位次。结合LIMIT限制返回结果有两种常用场景：

场景1：全表生成排名后取前N行 #

如果需要为所有记录按规则生成排名后，直接截取前N条返回，可直接在主查询后加LIMIT。示例为按card_id升序排序生成排名，返回前5条记录：

SELECT 
    students,
    stu_id,
    card_id,
    RANK() OVER (ORDER BY card_id ASC) AS ranking
FROM rankings
LIMIT 5;

场景2：返回排名位于前N位的所有记录 #

如果需要筛选排名本身在前N位的记录，需要将RANK()逻辑放在子查询中（窗口函数不能直接作为WHERE过滤条件），此时如果有多个记录排名相同，会全部返回，不会被LIMIT截断。示例为返回排名前3位的所有记录：

SELECT * FROM (
    SELECT 
        students,
        stu_id,
        card_id,
        RANK() OVER (ORDER BY card_id ASC) AS ranking
    FROM rankings
) AS ranked_result
WHERE ranking <= 3;

如果需要调整排序规则，只需修改OVER(ORDER BY 字段名 排序规则)部分即可，排序规则可选ASC（升序，默认）、DESC（降序）。

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