开窗函数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;
开窗函数使用于取出多列分组，取一列分组下另一组前几名，先利用开窗函数对其分组排名，开窗函数排名函数较多使用row_number()，还有rank()等，生成排名列之后将结果集筛选其排名前几<=2或者你想要的前几名。
上述执行结果：
image-the result of above example
获得每个test_name字段下test_id的test_salary的前2名。
开窗函数实例：
image-实例
另有开窗函数替代写法,可以了解：
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;
--开窗函数替代写法
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;
