;with f as ( select * from tb where composite=@composite union all select a.* from tb a join f b on a.single=b.composite ) select * from f
[code=SQL] create table #t(id int,childid int) insert into #t select 1 , 2 union all select 2 , 3 union all select 3 , 4 union all select 5 , 6;with t as ( select flag = 1 , * from #t where id = 1 --换成实际参数 union all select flag = t.flag + 1 ,#t.* from #t inner join t on #t.id = t.childid ) select top 1 childid from t order by flag descdrop table #t ---------------------------------------- childid ----------- 4(1 行受影响)/code]
create table #t(id int,childid int) insert into #t select 1 , 2 union all select 2 , 3 union all select 3 , 4 union all select 5 , 6;with t as ( select flag = 1 , * from #t where id = 1 --换成实际参数 union all select flag = t.flag + 1 ,#t.* from #t inner join t on #t.id = t.childid ) select top 1 childid from t order by flag descdrop table #t ---------------------------------------- childid ----------- 4(1 行受影响)
(
select * from tb where composite=@composite
union all
select a.* from tb a join f b on a.single=b.composite
)
select * from f
create table #t(id int,childid int)
insert into #t
select 1 , 2 union all
select 2 , 3 union all
select 3 , 4 union all
select 5 , 6;with t as
(
select flag = 1 , * from #t where id = 1 --换成实际参数
union all
select flag = t.flag + 1 ,#t.* from #t inner join t on #t.id = t.childid
)
select top 1 childid from t order by flag descdrop table #t
----------------------------------------
childid
-----------
4(1 行受影响)/code]
create table #t(id int,childid int)
insert into #t
select 1 , 2 union all
select 2 , 3 union all
select 3 , 4 union all
select 5 , 6;with t as
(
select flag = 1 , * from #t where id = 1 --换成实际参数
union all
select flag = t.flag + 1 ,#t.* from #t inner join t on #t.id = t.childid
)
select top 1 childid from t order by flag descdrop table #t
----------------------------------------
childid
-----------
4(1 行受影响)
MySQL中进行树状所有子节点的查询