With

WITH构造

WITH data1(id,parentid) AS ( select 2 id,1 parentid union select 3 id,1 parentid union select 4 id,1 parentid union select 5 id,2 parentid union select 6 id,5 parentid union select 7 id,6 parentid union select 1 id,0 parentid ) SELECT * FROM data1 ORDER BY id;

递归调用

with RECURSIVE data1(id,parentid) as ( select 2 id,1 parentid union select 3 id,1 parentid union select 4 id,1 parentid union select 5 id,2 parentid union select 6 id,5 parentid union select 7 id,6 parentid union select 1 id,0 parentid ), t as ( select data1.id,data1.parentid from data1 where parentid=0 union all select data1.id,data1.parentid from data1 join t on t.id=data1.parentid ) select * from t;

参考：postgresql 表达式_PostgreSQL-With子句