分组最大值记录
比如 序号 名称 数量 1 A 20 2 A 10 1 B 20 2 B 40 3 B 10 1 C 20 2 C 40 |
子查询:
select * from 表 where (序号,名称) in (select max(序号),名称 from 表 group by 名称)
分析函数:
select 序号 , 名称 , 数量 from
(select 序号 , 名称 , 数量
,row_number() over(partition by 名称 order by 序号 desc ) rn
form tab_name )
where rn=1
或
select 序号 , 名称 , 数量 from
(select 序号 , 名称 , 数量
, max(序号) over(partition by 名称) rn
form tab_name )
where rn=序号
注意:max的字段只能是number类型字段,如果是date类型的,会提示错误。date类型用上面的row_number()来做就可以了。
Oracle 分组 取第一条记录
id apply_id
1 1
2 1
3 1
4 2
5 2
6 3
7 3
8 3
取出
id apply_id
3 1
5 2
8 3
select alx_a.id
from
(select id,apply_id,rownum rid from 表) alx_a,
(select id,apply_id,rownum rid from 表) alx_b
where alx_a.apply_id = alx_b.apply_id and alx_a.id <= alx_b.id
group by alx_a.id,alx_a.apply_id
having count(*) = 1
1 1
2 1
3 1
4 2
5 2
6 3
7 3
8 3
取出
id apply_id
3 1
5 2
8 3
select alx_a.id
from
(select id,apply_id,rownum rid from 表) alx_a,
(select id,apply_id,rownum rid from 表) alx_b
where alx_a.apply_id = alx_b.apply_id and alx_a.id <= alx_b.id
group by alx_a.id,alx_a.apply_id
having count(*) = 1