PostgreSQL中按含重复值列实现游标分页的问题与解法

我正尝试对按评论数排序的帖子进行游标分页，但当排序列存在重复值时，无法正确对数据集进行切片。

表结构 #

create table posts
(
    id    uuid         not null primary key,
    title varchar(255) not null
);
create table comments
(
    id      uuid not null primary key,
    post_id uuid not null references posts
);

当前查询逻辑 #

先按评论数查询并排序帖子：

select 
  "posts".*, 
  count("comments"."id") as "comments_count"
from "posts"
left join "comments" on "posts"."id" = "comments"."post_id"
group by "posts"."id"
order by "comments_count" desc

随后通过CTE结合WHERE和LIMIT进行分页：

with "cte" as (
    {....上述查询语句....}
)
select *
from "cte"
where "comments_count" <= (select "comments_count" from "cte" where "id" = '00000000-0000-0000-0000-000000000003')
limit 3;

问题现象 #

当多行评论数相同时（例如ID为...0003和...0002的帖子评论数均为2），无法精准获取目标分页数据：

• 使用<=会返回...0003、...0002、...0006，包含了上一页的...0003
• 使用<会返回...0006、...0007、...0001，跳过了当前页应有的...0002

需求是不使用OFFSET分页，且无需大幅重构现有查询。

解决方案 #

核心思路是把唯一主键加入排序条件和分页过滤条件，让排序结果具备唯一性，从而实现精准的游标定位。

步骤1：修改排序逻辑，加入主键作为第二排序字段 #

通过主键补充排序维度，避免重复评论数导致的顺序歧义：

select 
  "posts".*, 
  count("comments"."id") as "comments_count"
from "posts"
left join "comments" on "posts"."id" = "comments"."post_id"
group by "posts"."id"
order by "comments_count" desc, "posts"."id" desc; -- 主键排序方向需与分页逻辑匹配

步骤2：修改分页过滤条件，同时匹配评论数和主键 #

基于上一页最后一条数据的comments_count和id，构建复合过滤条件：

with "cte" as (
    select 
      "posts".*, 
      count("comments"."id") as "comments_count"
    from "posts"
    left join "comments" on "posts"."id" = "comments"."post_id"
    group by "posts"."id"
    order by "comments_count" desc, "posts"."id" desc
)
select *
from "cte"
where 
  -- 评论数小于游标值，直接纳入结果
  ("comments_count" < (select "comments_count" from "cte" where "id" = '00000000-0000-0000-0000-000000000003'))
  OR 
  -- 评论数等于游标值时，只选取主键更小的记录（对应id desc排序，若为asc则改为id >）
  ("comments_count" = (select "comments_count" from "cte" where "id" = '00000000-0000-0000-0000-000000000003') AND "id" < '00000000-0000-0000-0000-000000000003')
order by "comments_count" desc, "posts"."id" desc
limit 3;

优化：避免重复子查询 #

将游标数据单独提取，减少CTE的重复计算：

with "post_stats" as (
    select 
      "posts".*, 
      count("comments"."id") as "comments_count"
    from "posts"
    left join "comments" on "posts"."id" = "comments"."post_id"
    group by "posts"."id"
),
"cursor_data" as (
    select "comments_count", "id" from "post_stats" where "id" = '00000000-0000-0000-0000-000000000003'
)
select *
from "post_stats"
cross join "cursor_data"
where 
  ("post_stats"."comments_count" < "cursor_data"."comments_count")
  OR 
  ("post_stats"."comments_count" = "cursor_data"."comments_count" AND "post_stats"."id" < "cursor_data"."id")
order by "comments_count" desc, "id" desc
limit 3;

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