17,382
社区成员




SQL> with t as(
2 select 1 duration,0.5 interest_rate,100 sal from dual union all
3 select 1,0.4,200 from dual union all
4 select 1,0.5,50 from dual union all
5 select 1,0.3,200 from dual union all
6 select 2,0.5,100 from dual union all
7 select 2,0.4,200 from dual union all
8 select 2,0.5,50 from dual union all
9 select 2,0.3,200 from dual)
10
10 select duration,interest_rate,sum(sal)
11 from t
12 where interest_rate in
13 (select max(interest_rate) from t)
14 group by duration,interest_rate;
/*
DURATION INTEREST_RATE SUM(SAL)
---------- ------------- ----------
1 0.5 150
2 0.5 150
*/
--分析函数好解决点,不过用子查询还是一样,随便你
with tab as(
select '1年' 期限, 0.5 利率, 100 金额 from dual union all
select '1年', 0.4, 200 from dual union all
select '1年', 0.5, 50 from dual union all
select '1年', 0.3, 200 from dual union all
select '2年', 0.5, 100 from dual union all
select '2年', 0.4, 200 from dual union all
select '2年', 0.5, 50 from dual union all
select '2年', 0.3, 200 from dual
)
--上面是测试数据
select 期限,
max(利率) 最大利率,
sum(金额) 最大利率金额,
max(总金额) 总金额
from(
select 期限,利率,金额,
dense_rank()over(partition by 期限 order by 利率 desc) lv,
Sum(金额)over(partition by 期限) 总金额
from tab
)
where lv=1
group by 期限
--结果:
期限 最大利率 最大利率金额 总金额
--------------------------------------
1年 0.5 150 550
2年 0.5 150 550