Postgres层级表查询如何仅对父级行应用LIMIT分页

方案1：适配仅2级层级（父-子）场景 #

如果你的数据只有「顶级+子级」两层固定层级，直接通过CTE先过滤出需要分页的顶级节点，再关联拉取所有关联节点即可：

WITH paginated_top AS (
  SELECT id
  FROM my_table
  WHERE parent_id IS NULL
  -- 此处LIMIT/OFFSET仅作用于顶级节点，子节点不会占用限额
  LIMIT _limit
  OFFSET _offset
)
SELECT *
FROM my_table
WHERE 
  -- 匹配顶级节点本身
  id IN (SELECT id FROM paginated_top)
  -- 匹配这些顶级节点的所有子节点
  OR parent_id IN (SELECT id FROM paginated_top)
-- 加排序规则方便后续业务代码组装层级结构
ORDER BY COALESCE(parent_id, id), id;

该方案逻辑简单，你设置LIMIT 1时就会返回1个顶级节点+它的全部子节点，完全匹配需求。

方案2：适配无限层级场景 #

如果你的数据不止两层层级（子节点下还有孙节点等多层结构），可以用Postgres的递归CTE实现：

WITH RECURSIVE paginated_top AS (
  -- 第一步：先分页筛选目标顶级节点
  SELECT id
  FROM my_table
  WHERE parent_id IS NULL
  LIMIT _limit
  OFFSET _offset
),
tree AS (
  -- 递归初始值：选中的所有顶级节点
  SELECT t.*
  FROM my_table t
  JOIN paginated_top pt ON t.id = pt.id
  UNION ALL
  -- 递归拉取所有后代子节点
  SELECT t.*
  FROM my_table t
  JOIN tree tr ON t.parent_id = tr.id
)
SELECT * FROM tree
ORDER BY COALESCE(parent_id, id), id;

优化建议 #

• 数据量较大的场景下，建议给parent_id字段建索引，可大幅提升关联查询、递归查询的效率
• 如果你不想在数据库侧做递归处理，也可以先分页查询出顶级节点ID列表，再单独查询所有父链属于该列表的节点，在业务侧拼装层级结构，逻辑和上述方案完全一致。

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