所以我有一个数据表,它有3列,1列是引用号,另一列是该引用的版本,第三列是属于该引用的项。
我想做的是显示一个显示3列的表,第一列是参考号,下一列是版本1上的项目列表和"最后一个最终版本"上的项目清单。
原始数据:
refno | 版本 | 项目|
---|---|---|
1 | 1 | ABC123 |
1 | 1 | XYZ123 |
1 | 2 | EFG123 |
2 | 1 | UIO123 |
2 | 1 | JKL123 |
3 | 1 | ABC123 |
3 | 2 | ABC123 |
3 | 2 | HJF123|
3 | 2 | IKJ123|
3 | 2 | EEK123|
3 | 2 | EEK123|
4 | 1 | GFD123 |
4 | 1 | YUI123 |
4 | 2 | YUI123|
5 | 1 | 典型123 |
6 | 1 | GHS123|
7 | 1 | TEP123 |
7 | 1 | SLS123|
7 | 2 | TEP123|
7 | 2 | SLS123|
7 | 2 | AEE123|
7 | 3 | AAL123|
7 | 4 | QEF123 |
看起来您想要完全自加入
select coalesce(a.refno, b.refno) refno, a.item OriginalItem, b.item FinalItem
from(
select *
from t
where version = 1
) a
full join(
select *
from (
select *, rank() over(partition by refno order by version DESC) rn
from t
where version > 1
) t
where rn=1
) b
on a.refno=b.refno
order by coalesce(a.refno, b.refno), a.item, b.item
返回
refno OriginalItem FinalItem
1 ABC123 EFG123
1 XYZ123 EFG123
2 JKL123
2 UIO123
3 ABC123 ABC123
3 ABC123 EEK123
3 ABC123 EEK123
3 ABC123 HJF123
3 ABC123 IKJ123
4 GFD123 YUI123
4 YUI123 YUI123
5 TYP123
6 GHS123
7 SLS123 QEF123
7 TEP123 QEF123