Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.5k views
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

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
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 OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...