1.開窗函數(shù)over partition
????開窗函數(shù)使用于取出多列分組喧锦，取一列分組下另一組前幾名，先利用開窗函數(shù)對其分組排名，開窗函數(shù)排名函數(shù)較多使用row_number()恰起，還有rank()等，生成排名列之后將結果集篩選其排名前幾<=2或者你想要的前幾名趾牧。
上述例子sql：
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;
上述執(zhí)行結果：
? ? 獲得每個test_name字段下test_id的test_salary的前2名检盼。
開窗函數(shù)實例：
另有開窗函數(shù)替代寫法,可以了解：
