获取项目的所有祖先的 SQL 递归查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16749095/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL recursive query that gets all ancestors of an item
提问by Kld
ID parent_id name
---------------------
1 2 first
2 4 second
3 3 third
4 5 fourth
5 - fifth
Ancestors list of firstshould be (2, 4, 5)
祖先名单 first应该是 (2, 4, 5)
回答by a_horse_with_no_name
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
SQLFiddle：http://sqlfiddle.com/#!3/87d0c/1
回答by Ian Preston
You can use something like this:
你可以使用这样的东西：
with parents as
(
select ID, parent_ID
from t
where parent_ID is not null
union all
select p.ID, t.parent_ID
from parents p
inner join t on p.parent_ID = t.ID
and t.parent_ID is not null
and t.ID <> t.parent_ID
)
select *
, parents = '(' + stuff
(
(
select ', ' + cast(p.parent_ID as varchar(100))
from parents p
where t.ID = p.ID
for xml path('')
), 1, 2, ''
) + ')'
from t
order by ID
This combines two very common T-SQL techniques - using a CTEto get a hierarchy and using FOR XML PATH to get a CSV list.
这结合了两种非常常见的 T-SQL 技术 - 使用CTE获取层次结构和使用 FOR XML PATH 获取 CSV 列表。
