Oracle 递归查询树（start with ... connect by prior）(prior 代表上一条数据)
select * from sr_menu start with id = 1 connect by prior id = parent;
postgresql 具有类型的功能 分层查询(with recursive)
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;
递归WITH的执行流程：
计算非递归项（UNION ALL内的固定查询部分，例如上面的VALUES(1)）把结果放在临时表A中
临时表不为空，重复下列步骤：
计算递归项（UNION ALL 内的递归部分），用临时表A当作递归自引用表。查询结果记录到临时表B
用B的数据库覆盖A，清空B。
WITH RECURSIVE a AS ( SELECT id, parent, title, 1::integer recursion_level FROM sr_menu WHERE id = 1 UNION ALL SELECT d.id, d.parent, d.title, a.recursion_level +1 FROM sr_menu d JOIN a ON a.id = d.parent ) SELECT * FROM a;
原文：https://www.cnblogs.com/pass-ion/p/14649911.html