if object_id('tb','U')is not null drop table tb go create table tb(ID int, ParentID int) insert into tb select 1,0 insert into tb select 2,1 insert into tb select 3,1 insert into tb select 4,2 insert into tb select 5,3 insert into tb select 6,5 insert into tb select 7,6;WITH cte AS ( SELECT *,[level]=0 FROM tb WHERE ID=5 UNION ALL SELECT tb.*,[level]=[level]+1 from cte ,tb WHERE cte.ParentID=tb.ID ) SELECT * FROM cte WHERE [level]=2是这个第二级吗?加了level 显示级,你在后面选择需要的级别就可以了
谢谢你滴回复哈,我现在想要的结果是 insert into tb select 1,0 insert into tb select 2,1 insert into tb select 3,1 insert into tb select 4,2 insert into tb select 5,3 insert into tb select 6,5 insert into tb select 7,6 insert into tb select 8,3 insert into tb select 9,4 insert into tb select 10,6 现在又id 8 9 10 我查出的结果为2,3 就是这个ID所属的父类的别第二级别,不知道我滴表达你明白了吗? 谢谢你哈
with t as ( select * from tb where id in (8,9,10) union all select tb.id, tb.parentid from t, tb where tb.id=t.parentid ) select distinct t.id from t,(select id from tb where parentid=1) b where t.id=b.id
go
create table tb(ID int, ParentID int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6;WITH cte AS
(
SELECT *,[level]=0 FROM tb WHERE ID=5
UNION ALL
SELECT tb.*,[level]=[level]+1 from cte ,tb WHERE cte.ParentID=tb.ID
)
SELECT * FROM cte WHERE [level]=2是这个第二级吗?加了level 显示级,你在后面选择需要的级别就可以了
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,3
insert into tb select 6,5
insert into tb select 7,6
insert into tb select 8,3
insert into tb select 9,4
insert into tb select 10,6
现在又id 8 9 10 我查出的结果为2,3 就是这个ID所属的父类的别第二级别,不知道我滴表达你明白了吗?
谢谢你哈
(
select * from tb where id in (8,9,10) union all
select tb.id, tb.parentid from t, tb where tb.id=t.parentid
)
select distinct t.id from t,(select id from tb where parentid=1) b where t.id=b.id