如何使用CTE映射父子关系? [英] How to use CTE to map parent-child relationship?
本文介绍了如何使用CTE映射父子关系?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
说,我有一个表,代表一个树状结构化数据,我想不断向上跟踪,直到到达顶层节点,并以parent_id为NULL标记。我的MS SQL CTE (公用表表达式)是什么样的?
Say I have a table of items representing a tree-like structured data, and I would like to continuously tracing upward until I get to the top node, marked by a parent_id of NULL. What would my MS SQL CTE (common table expression) look like?
例如,如果我要获取到达 Bender 中的顶部,看起来像是
For example, if I were to get the path to get to the top from Bender, it would look like
喜剧
Futurama
弯曲器
谢谢,这是示例数据:
DECLARE @t Table(id int, description varchar(50), parent_id int)
INSERT INTO @T
SELECT 1, 'Comedy', null UNION
SELECT 2, 'Futurama', 1 UNION
SELECT 3, 'Dr. Zoidberg', 2 UNION
SELECT 4, 'Bender', 2 UNION
SELECT 5, 'Stand-up', 1 UNION
SELECT 6, 'Unfunny', 5 UNION
SELECT 7, 'Dane Cook', 6
推荐答案
看起来像这样:
declare @desc varchar(50)
set @desc = 'Bender'
;with Parentage as
(
select * from @t where description = @desc
union all
select t.*
from @t t
inner join Parentage p
on t.id = p.parent_id
)
select * from Parentage
order by id asc --sorts it root-first
这篇关于如何使用CTE映射父子关系?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文