PostgreSQL使用DENSE_RANK按指定规则生成排名实现关联数据分页咨询

这个需求完全可以实现，核心思路是先固定原始排序的行顺序，再以每个ID首次出现的位置作为排名依据，既保证相同ID排名一致，也不会打乱原有查询的行序。

具体实现SQL #

WITH sorted_rows AS (
    -- 保留你原来的查询逻辑和排序规则，给每行分配唯一的原始行号
    SELECT 
        id,
        is_smth,
        some_date,
        minutes,
        ROW_NUMBER() OVER (ORDER BY some_date ASC NULLS LAST, minutes DESC) AS original_row_num
    FROM 你的业务表名
    -- 这里补充你原来的WHERE筛选条件
),
id_first_pos AS (
    -- 计算每个ID第一次在结果中出现的行号
    SELECT 
        id,
        MIN(original_row_num) AS first_occur_pos
    FROM sorted_rows
    GROUP BY id
),
ranked_result AS (
    -- 关联得到每个ID的排名
    SELECT 
        sr.*,
        DENSE_RANK() OVER (ORDER BY ifp.first_occur_pos) AS rank
    FROM sorted_rows sr
    INNER JOIN id_first_pos ifp ON sr.id = ifp.id
)
-- 筛选排名前2的所有行，同时保留原始排序
SELECT id, is_smth, some_date, minutes, rank
FROM ranked_result
WHERE rank <= 2
ORDER BY original_row_num;

效果说明 #

执行上述SQL后，返回的结果和你预期的完全一致：

筛选rank <= 2即可取出ID为35、36的所有关联行，完全符合你的分页需求。

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