一、创建表
CREATE TABLE test_rank_sales (
location_name VARCHAR2(20),
month_flag number,
sales NUMBER,
manager VARCHAR2(20),
create_stamp DATE);
二、插入数据
insert into test_rank_sales values('SH',5,100000,'Kevin',sysdate);
/
insert into test_rank_sales values('SH',6,900000,'Kevin',sysdate);
/
insert into test_rank_sales values('SH',7,900000,'Kevin',sysdate);
/
insert into test_rank_sales values('HZ',5,100000,'JT',sysdate);
/
insert into test_rank_sales values('HZ',6,910000,'JT',sysdate);
/
insert into test_rank_sales values('HZ',7,890000,'JT',sysdate);
/
insert into test_rank_sales values('GZ',5,100000,'Miles',sysdate);
/
insert into test_rank_sales values('GZ',6,990000,'Miles',sysdate);
/
insert into test_rank_sales values('GZ',7,890000,'Miles',sysdate);
/
insert into test_rank_sales values('BJ',5,100000,'Collion',sysdate);
/
insert into test_rank_sales values('BJ',6,910000,'Collion',sysdate);
/
insert into test_rank_sales values('BJ',7,990000,'Collion',sysdate);
/
commit;
三、找出5,6,7月内销售业绩最好的manager
select a.manager
from (select manager, sum(sales) A_SALES
from test_rank_sales
group by manager) a,
(select max(TEMP_SALES.TOTAL_SALES) B_SALES
from (select manager, sum(sales) TOTAL_SALES
from test_rank_sales
group by manager) TEMP_SALES) b
where a.A_SALES = b.B_SALES
SQL> select a.manager
2 from (select manager, sum(sales) A_SALES
3 from test_rank_sales
4 group by manager) a,
5 (select max(TEMP_SALES.TOTAL_SALES) B_SALES
6 from (select manager, sum(sales) TOTAL_SALES
7 from test_rank_sales
8 group by manager) TEMP_SALES) b
9 where a.A_SALES = b.B_SALES
10 ;
MANAGER
--------------------
Collion
SQL>
另外一种解决方案:
SQL> select manager
2 from (select manager,
3 sum(sales),
4 rank() over(order by sum(sales) desc) as FINAL_ORDER
5 from test_rank_sales
6 group by manager)
7 where FINAL_ORDER = 1
8 ;
MANAGER
--------------------
Collion
注:rank()根据order by排序结果,计算组间相对位置,有跳号,它是一个数据排序的函数。
SQL> select manager,
2 sum(sales) ,
3 rank() over( order by sum(sales) desc) as FINAL_ORDER
4 from test_rank_sales
5 group by manager;
MANAGER SUM(SALES) FINAL_ORDER
-------------------- ---------- -----------
Collion 2000000 1
Miles 1980000 2
JT 1900000 3
Kevin 1900000 3
SQL>