SQL 递回 写法(OLD 及 NEW)

Question

with T as(
select 1 SN,'1' Id,'212' Old,'302' New
union 
select 2 SN,'1' Id,'302' Old,'212' New
union 
select 3 SN,'1' Id,'212' Old,'304' New
union 
select 4 SN,'1' Id,'215' Old,'302' New)
select T.* from T ORDER BY SN

我要下什么SQL可以取得这结果

Id    Old    New

1     212   304

1     215   302

 

Answer 1

下面是sqlserver版本

with T as(
select 1 SN,'1' Id,'212' Old,'302' New
union 
select 2 SN,'1' Id,'302' Old,'212' New
union 
select 3 SN,'1' Id,'212' Old,'304' New
union 
select 4 SN,'1' Id,'215' Old,'302' New),
tt as (
select T.*,old root_value from T  where sn=1 /*第一个根节点*/
union all
select t.*,tt.root_value from t,tt where t.sn=tt.sn+1 and tt.new=t.old /*下一个节点*/
union all
select  t.*,t.old from t,tt where t.sn=tt.sn+1 and tt.new<>t.old /*新的根节点*/
)

select id,old,new from (
select tt.*,row_number() over(partition by root_value order by sn desc) rn from tt) as x
where rn=1 /*按根节点进行分组排序，取最后一个*/