sql - 使用 PostgreSQL 进行多项路径查询

Question

使用以下 PostgreSQL 9.2.2 表:

  id    | parent_id
--------+----------
 body   | null
 head   | body
 mouth  | head
 eye    | head
 tooth  | mouth
 tongue | mouth
 sclera | eye
 cornea | eye

我需要一个输出，其中列出了每个非 parent child 的所有直接间接 parent ，如下所示:

tooth   mouth
tooth   head
tooth   body
tongue  mouth
tongue  head
tongue  body
sclera  eye
sclera  head
sclera  body
cornea  eye
cornea  head
cornea  body

我试过搜索，我的结果只显示使用递归的单项查询，比如:

WITH RECURSIVE t AS ( 
SELECT  parent_id, id 
FROM    item_tree 
WHERE   child_id = id 
UNION ALL 
SELECT  it.parent_id, it.id
    FROM    item_tree it 
JOIN    t 
ON  it.child_id = t.parent_id
) 
SELECT  id, child_id 
FROM    t 

我可以在外部编写一个循环，每次都替换 id，但是只能在 SQL 中完成吗？

@丹尼尔，

这是您原始查询的输出:

  id     parent_id
  ------ ---------
  cornea eye
  cornea NULL
  cornea head
  cornea body
  sclera eye
  sclera head
  sclera NULL
  sclera body
  tongue body
  tongue head
  tongue NULL
  tongue mouth
  tooth  body
  tooth  head
  tooth  mouth
  tooth  NULL 

但是，如果您使用空过滤的 select 语句将其括起来，即使您删除了内部空过滤器，它也会提供所需的结果，如下所示:

  select * from (
     WITH RECURSIVE t(id,parent_id) AS ( 
     select id,parent_id from item_tree i
     UNION ALL
     select t.id,i.parent_id from item_tree i JOIN t on i.id=t.parent_id 
     )
     select * from t order by id
  ) t1 where parent_id is not null;

无论如何，我已经点击了复选标记，因为这可能是一个错误(我已经尝试通过 jdbc 和 pgAdmin3 运行两个查询，输出相同)

Answer 1

假设表的结构是item_tree(id 文本，parent_id 文本)，递归查询可以从您定义的叶元素开始它们(即，它们不是任何东西的父元素)，并且具有空 parent_id 的顶级元素也必须被过滤:

select id,parent_id from item_tree i
 where parent_id is not null and
  id not in  (select parent_id from item_tree where parent_id is not null)

然后将 (parent_id,id) 关系爬到树的顶部。

完成查询:

WITH RECURSIVE t(id,parent_id) AS ( 
select id,parent_id from item_tree i where parent_id is not null 
   and id not in  (select parent_id from item_tree where parent_id is not null)
UNION ALL
 select t.id,i.parent_id from item_tree i JOIN t on i.id=t.parent_id 
)
select * from t order by id;

编辑:上面的查询在其输出中包含层次结构顶部的 NULL。要排除它们，可以改用此修改版本。最终结果应与使用外部过滤的已编辑问题中的查询相同。

WITH RECURSIVE t(id,parent_id) AS ( 
select id,parent_id from item_tree i where parent_id is not null 
   and id not in  (select parent_id from item_tree where parent_id is not null)
UNION ALL
 select t.id,i.parent_id from item_tree i JOIN t on i.id=t.parent_id
   where i.parent_id is not null 
)
select * from t order by id;