問題描述

我有一個(gè)表，其中包含如下建模的數(shù)據(jù)對(duì):

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 大.這些對(duì)代表要進(jìn)行的替換.所以100會(huì)被50代替，然后50會(huì)被40代替，40會(huì)被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.

所以結(jié)果是這樣的:

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

有沒有一種簡潔的方式可以改變或加入這張表來表示這一點(diǎn)?

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

結(jié)果:

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

這篇關(guān)于SQL 將表數(shù)據(jù)修改為更緊湊的形式的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！