SQL revising table data to a more compact form

问题:

I have a table with data pairs modeled like the following:

Id1    Id2
-----------
100    50
120    70
70     50
34     20
50     40
40     10

Id1 is always bigger then Id2. The pairs represent replacements to be made. So 100 will be replaced with 50, but then 50 will be replaced with 40, which will then be replaced by 10.

So the result would be like this:

Id1    Id2
-----------
100    10
120    10
34     20

Is there a nice succinct way that I can alter, or join this table to represent this?

I know i can join it on itself something akin to:

SELECT t1.Id1, t2.Id2
  FROM mytable t1
  JOIN myTable t2 ON t2.Id1 = t1.Id2

But this will require several passes, hence why i ask if there is a nicer way to accomplish it?

回答1:

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.id1
from a
join @t t on a.id2 = t.id1
)
-- show the lowest child for each row found in <*>
select id1, min(id2) id2 from a
group by id1

Result:

id1         id2
----------- -----------
34          20
100         10
120         10