例子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=1008000000000773649
union all select bis_industry.* from bis_industry,parent where bis_industry.id=parent.pid
)
select id,pid,n from parent;