Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others


0 votes
in Technique[技术] by (71.8m points)

sql server - How to use CTE to map parent-child relationship?

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




Thanks, and here's the sample data:

DECLARE @t Table(id int, description varchar(50), parent_id int)

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
See Question&Answers more detail:os

Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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 

Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share