開窗函數(shù)over partition
select test_name, test_id, n_rank, test_salary_sum
from (select t_alias.test_name,
t_alias.test_id,
sum(t_alias.test_salary) test_salary_sum,
row_number() over(partition by t_alias.test_name order by sum(t_alias.test_salary) desc) n_rank
from (select 1 test_id, 'name_1' test_name, 100 test_salary
from dual
union all
select 2 test_id, 'name_1' test_name, 200 test_salary
from dual
union all
select 2 test_id, 'name_1' test_name, 300 test_salary
from dual
union all
select 3 test_id, 'name_1' test_name, 300 test_salary
from dual
union all
select 4 test_id, 'name_1' test_name, 400 test_salary
from dual
union all
select 1 test_id, 'name_2' test_name, 200 test_salary
from dual
union all
select 2 test_id, 'name_2' test_name, 200 test_salary
from dual
union all
select 2 test_id, 'name_2' test_name, 200 test_salary
from dual
union all
select 3 test_id, 'name_2' test_name, 400 test_salary
from dual
union all
select 4 test_id, 'name_2' test_name, 400 test_salary
from dual) t_alias
group by t_alias.test_name, t_alias.test_id)
where n_rank <= 2
order by test_name, n_rank;
開窗函數(shù)使用于取出多列分組察迟，取一列分組下另一組前幾名隐绵，先利用開窗函數(shù)對其分組排名愧怜，開窗函數(shù)排名函數(shù)較多使用row_number()，還有rank()等则奥，生成排名列之后將結(jié)果集篩選其排名前幾<=2或者你想要的前幾名考润。
上述執(zhí)行結(jié)果：
image-the result of above example
獲得每個test_name字段下test_id的test_salary的前2名。
開窗函數(shù)實例：
image-實例
另有開窗函數(shù)替代寫法,可以了解：
select * from adminus;
create table adminus_test(num number(5,2),count number(5,2));
insert into adminus_test(num,count) values(1,5);
insert into adminus_test(num,count) values(1,7);
insert into adminus_test(num,count) values(1,8);
insert into adminus_test(num,count) values(2,8);
insert into adminus_test(num,count) values(2,9);
insert into adminus_test(num,count) values(2,7);
select * from adminus_test;
--開窗函數(shù)替代寫法
select d.admin_id, d.num, c.count
from (select num,
count,
(select count(1)
from adminus_test b
where b.num = a.num
and a.count <= b.count) as cnt --等級的變相的寫法 --重要寫法
from adminus_test a
group by num, count) c
join adminus d
on c.num = d.num
where c.cnt <= 2;
