如何用单条PostgreSQL查询获取指定用户的Top N条高分帖子

要实现给指定用户（如user_id=10和20）各自获取按Score降序排列的前2条帖子，用PostgreSQL的窗口函数可以轻松完成单条查询的需求。

假设表结构与示例数据 #

先定义Posts表的基础结构，并插入测试数据：

CREATE TABLE Posts (
    post_id INT PRIMARY KEY,
    user_id INT,
    score INT,
    content TEXT
);

INSERT INTO Posts VALUES
(1, 10, 95, 'Post A'),
(2, 10, 88, 'Post B'),
(3, 10, 92, 'Post C'),
(4, 20, 80, 'Post D'),
(5, 20, 90, 'Post E'),
(6, 20, 85, 'Post F'),
(7, 30, 98, 'Post G'); -- 无关用户，用于验证过滤逻辑

核心查询语句 #

SELECT post_id, user_id, score, content
FROM (
    SELECT 
        post_id, 
        user_id, 
        score, 
        content,
        -- 按用户分组，组内按分数降序分配行号
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY score DESC) AS rn
    FROM Posts
    -- 过滤出目标用户
    WHERE user_id IN (10, 20)
) ranked_posts
-- 只保留每组的前2条
WHERE rn <= 2
-- 按用户ID、分数降序排序结果
ORDER BY user_id, score DESC;

查询逻辑说明 #

1. 内层子查询：使用ROW_NUMBER()窗口函数，按user_id分组（PARTITION BY user_id），每组内按score降序排序，为每条帖子分配唯一行号rn。
2. 外层过滤：筛选出行号rn <=2的记录，即每个用户的前2条高分帖子。
3. 结果排序：最终按用户ID和分数降序排列，让结果更直观。

执行结果 #

扩展说明 #

如果存在分数并列的情况，若想保留所有并列的TopN记录，可以替换窗口函数：

• 使用RANK()：同分数会获得相同行号，若有多个并列第二名，会返回多于2条记录。
• 使用DENSE_RANK()：与RANK()类似，但行号不会跳过并列值。

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