如何利用CTE来映射父子关系？

发布于 2024-08-18 11:51:46 字数 624 浏览 5 评论 0原文

假设我有一个代表树状结构化数据的项目表，我想连续向上追踪，直到到达顶部节点，该节点由 NULL 的parent_id 标记。我的 MS SQL CTE（公用表表达式）会是什么样子？

例如，如果我要获取从 Bender 到达顶部的路径，它看起来会像

Comedy

Futurama

Bender< /strong>

谢谢，这是示例数据：

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

原文

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?

For example, if I were to get the path to get to the top from Bender, it would look like

Comedy

Futurama

Bender

Thanks, and here's the sample data:

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

紫轩蝶泪 2024-08-25 11:51:46

它应该看起来像这样：

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

it should look like this:

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

回复收藏 0 原文

~没有更多了~