declare @t table(Id1 int, Id2 int)insert @t values (100, 50)insert @t values ( 120, 70)insert @t values ( 70, 50)insert @t values ( 34, 20)insert @t values ( 50, 40)insert @t values ( 40, 10);with a as(-- find all rows without parent <*>select id2, id1 from @t t where not exists (select 1 from @t where t.id1 = id2)union all -- recusive work down to lowest child while storing the parent id1 select t.id2 , a.id1from ajoin @t t on a.id2 = t.id1)-- show the lowest child for each row found in <*>select id1, min(id2) id2 from agroup by id1
结果：
id1 id2----------- -----------34 20100 10120 10
