SQL 将表数据修改为更紧凑的形式

问题描述

我有一个表，其中包含如下建模的数据对:

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 总是比 Id2 大.这些对代表要进行的替换.所以100会被50代替，然后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.

所以结果是这样的:

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?

推荐答案

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

结果:

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