create table t_temp(
id number,
num1 number,
num2 number
)insert into t_temp values('1','2','2');
insert into t_temp values('2','2','3');
insert into t_temp values('3','2','');
select * from t_temp t;
ID NUM1 NUM2
---------- ---------- ----------
1 2 2
2 2 3
3 2
我要取出以num1为分组,id最大,num2不为空的值
也就是num2为3的值
id number,
num1 number,
num2 number
)insert into t_temp values('1','2','2');
insert into t_temp values('2','2','3');
insert into t_temp values('3','2','');
select * from t_temp t;
ID NUM1 NUM2
---------- ---------- ----------
1 2 2
2 2 3
3 2
我要取出以num1为分组,id最大,num2不为空的值
也就是num2为3的值
(select row_number() over(partition by num1 order by id desc) rn,t_temp.*
from t_temp
where num2 is not null) t
where rn=1;
SQL> edit
已写入 file afiedt.buf 1 with tt as(select '1' id, '2' num1, '2' num2 from dual
2 union all select '2', '2', '3' from dual
3 union all select '3', '2', '' from dual)
4 select * from tt a where
5 not exists(select 1 from tt b where b.id > a.id and b.num1 = a.num1 and b.n
um2 is not null) and
6* a.num2 is not null
SQL> /I N N
- - -
2 2 3SQL>