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
相关文章