获取项目的所有祖先的 SQL 递归查询 [英] SQL recursive query that gets all ancestors of an item

问题描述

ID       parent_id   name
---------------------
1        2            first 
2        4            second
3        3            third
4        5            fourth
5        -           fifth

first 的祖先列表应该是 (2, 4, 5)

推荐答案

with name_tree as (
   select id, parent_id, name
   from the_unknown_table
   where id = 1 -- this is the starting point you want in your recursion
   union all
   select c.id, c.parent_id, c.name
   from the_unknown_table c
     join name_tree p on p.parent_id = c.id  -- this is the recursion
) 
select *
from name_tree
where id <> 1; -- exclude the starting point from the overall result

SQLFiddle:http://sqlfiddle.com/#!3/87d0c/1

这篇关于获取项目的所有祖先的 SQL 递归查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！