数据格式:
单位编号 金额 考核期
2001 100.00 201201
2001 200.00 201202
2001 300.00 201202
2002 100.00 201104
2002 200.00 201201我想得到相同单位编号最大考核期的金额合计数,如上数据想得到结果:
2001 500.00 201202
2002 200.00 201201
单位编号 金额 考核期
2001 100.00 201201
2001 200.00 201202
2001 300.00 201202
2002 100.00 201104
2002 200.00 201201我想得到相同单位编号最大考核期的金额合计数,如上数据想得到结果:
2001 500.00 201202
2002 200.00 201201
group by 单位编号;
with t as (
select 2001 as unit, 100.00 as amount, 201201 as priod from dual
union all
select 2001, 200.00, 201202 from dual
union all
select 2001, 300.00, 201202 from dual
union all
select 2002, 100.00, 201104 from dual
union all
select 2002, 200.00, 201201 from dual
)
select unit,sum(amount) as smount,priod from (
select unit,amount,priod,dense_rank()over(partition by unit order by priod desc) as denseRank from t
) where denserank=1
group by unit,priod;--结果
UNIT SMOUNT PRIOD
---------------------- ---------------------- ----------------------
2001 500 201202
2002 200 201201