postgres 有递归查询,说白了就是广度遍历,但是没有层次查询,postgres plus 可以模拟 oracle——包括plsql存储过程等等语言特性,但是要钱。
怎么搞出层次查询呢?这里有个方法:
http://explainextended.com/2009/07/17/postgresql-8-4-preserving-order-for-hierarchical-query/
关键点:
with recursive t as (
select n.*, ARRAY[n.id] AS breadcrumb from test_node n where id=1
union all
select n.*, t.breadcrumb || n.id from test_node n, t where n.parent_id = t.id)
select * from t order by breadcrumb
埋一个 breadcrumb (面包屑)