pgsql 递归查询 分页

--向下查询
WITH RECURSIVE res AS ( 
       SELECT * FROM t_tree WHERE id = 2
     union   ALL 
       SELECT t_tree.* FROM t_tree, res WHERE t_tree.pid = res.id 
     ) 
SELECT * FROM res ORDER BY id limit 3 OFFSET (2-1)*3;--分页

--向上
WITH RECURSIVE res AS ( 
       SELECT * FROM t_tree WHERE id = 2
             union   ALL 
       SELECT t_tree.* FROM t_tree, res WHERE t_tree.id = res.pid 
       --select * from t_tree where t_tree.id = (SELECT t_tree.pid FROM t_tree WHERE id = 2) --id为id为2的记录的父id的记录，即向上查询
     ) 
SELECT * FROM res ORDER BY id;

适用于单继承关系（每一条记录只有一个父级）

转载于:https://www.cnblogs.com/oyx305/p/5683120.html