查询树形结构

两种查询树形结构的sql

table

id  upperid

1      2

3      2

4      1

5      3

现查询父id为2的所有节点

结果应该是：1，2，4，5

 

create table tbTest(id int, upperid int)
insert tbTest
select 1,     2 union all
select 3,     2 union all
select 4,     1 union all
select 5,     3

 

 

method first：

declare @t table(id int)
declare @id int
set @id=2
insert @t select id from tbTest where upperid = @id
while @@rowcount > 0
    insert @t select a.id from tbTest as a inner join @t as b
    on a.upperid = b.id and a.id not in(select id from @t)

 

 

method second：
with children as(
select * from tbTest Where upperid=2
 union all
select tbTest.* from children inner join tbTest on children.id=tbTest.upperid
)
select * from children