--示例--示例数据 create table tb(p varchar(10),c varchar(10)) insert tb select 'a','b' union all select 'b','d' union all select 'a','e' union all select 'd','f' union all select 'e','g' go--查询 create table #t(item varchar(10),level int) declare @l int set @l=1 insert #t select distinct p,@l from tb a where not exists( select * from tb where c=a.p) while @@rowcount>0 begin set @l=@l+1 insert #t select distinct a.c,@l from tb a,#t b where a.p=b.item and b.level=@l-1 end select * from #t drop table #t go--删除测试 drop table tb/*--测试结果item level ---------- ----------- a 1 b 2 e 2 d 3 g 3 f 4(所影响的行数为 6 行) --*/
create table tb(p varchar(10),c varchar(10))
insert tb select 'a','b'
union all select 'b','d'
union all select 'a','e'
union all select 'd','f'
union all select 'e','g'
go--查询
create table #t(item varchar(10),level int)
declare @l int
set @l=1
insert #t select distinct p,@l
from tb a
where not exists(
select * from tb where c=a.p)
while @@rowcount>0
begin
set @l=@l+1
insert #t select distinct a.c,@l
from tb a,#t b
where a.p=b.item and b.level=@l-1
end
select * from #t
drop table #t
go--删除测试
drop table tb/*--测试结果item level
---------- -----------
a 1
b 2
e 2
d 3
g 3
f 4(所影响的行数为 6 行)
--*/