;with t as( select * from @tb where id='9' union all select a.* from @tb a ,t where a.Id=t.fid) select * from t
你的ID=9时,FID=7,你的表中只有这样一笔数据. 怎么可能得到五笔数据的结果?
DECLARE @tb Table([id] int,fid int) insert @tb select 1,0 union all select 2,0 union all select 3,2 union all select 4,1 union all select 5,3 union all select 6,3 union all select 7,5 union all select 8,4 union all select 9,7;with t as( select * from @tb where id='9' union all select a.* from @tb a ,t where a.Id=t.fid) select * from t order by id/* id fid ----------- ----------- 2 0 3 2 5 3 7 5 9 7(5 行受影响) */
;with t as(
select * from @tb where id='9'
union all
select a.* from @tb a ,t where a.Id=t.fid)
select * from t
怎么可能得到五笔数据的结果?
DECLARE @tb Table([id] int,fid int)
insert @tb
select 1,0 union all
select 2,0 union all
select 3,2 union all
select 4,1 union all
select 5,3 union all
select 6,3 union all
select 7,5 union all
select 8,4 union all
select 9,7;with t as(
select * from @tb where id='9'
union all
select a.* from @tb a ,t where a.Id=t.fid)
select * from t order by id/*
id fid
----------- -----------
2 0
3 2
5 3
7 5
9 7(5 行受影响)
*/