例子1：1到100的和 WITH RECURSIVE t(n) AS ( VALUES (1) union ALL SELECT n+1 FROM t WHERE n < 100 ) SELECT sum(n) FROM t; 输出结果：5050 例子2：取得一棵树的所有孩子节点 WITH RECURSIVE r AS ( SELECT * FROM tree WHERE id = 1 union ALL SELECT tree.* FROM tree, r WHERE tree.parent = r.id ) SELECT * FROM r ORDER BY id; id | parent ----+-------- 1 | 2 | 1 3 | 1 4 | 3
例子3：取得一棵树的所有父节点
with recursive parent as (select * from bis_industry where id=1008000000000773649union all select bis_industry.* from bis_industry,parent where bis_industry.id=parent.pid)select id,pid,n from parent;
转载于:https://www.cnblogs.com/bjlpd/archive/2013/05/10/3071152.html
