17,086
社区成员
发帖
与我相关
我的任务
分享
with table_name(id,col1,col2,c)as(
select 1,null,'S1','C1' from dual
union all
select 2,'N2',null,'C1' from dual
union all
select 3,'N3','S3','C2' from dual
)
SELECT id, col1, col2, c
FROM (SELECT id,
MAX(t.col1) over(PARTITION BY t.c) col1, --此处只写了c字段相同分组 楼主多个自行修改
MAX(t.col2) over(PARTITION BY t.c) col2,
row_number() over(PARTITION BY t.c ORDER BY t.id DESC) rn,
t.c
FROM table_name t)
WHERE rn = 1;
--结果:
ID COL1 COL2 C
---------- ---- ---- --
2 N2 S1 C1
3 N3 S3 C2
select
NVL(A.C1,B.C1) C1,
NVL(A.C2,B.C2) C2,
NVL(A.C3,B.C3) C3,
NVL(A.C4,B.C4) C4
……
FROM T A,T B
WHERE A.id >B.id