案例來源:ITPUB
create table my_test(
student_id int,
class_id varchar(64),
score int
);
insert into my_test(student_id, class_id, score) values (1, 'c1', 99);
insert into my_test(student_id, class_id, score) values (1, 'c2', 90);
insert into my_test(student_id, class_id, score) values (1, 'c3', 87);
insert into my_test(student_id, class_id, score) values (2, 'c2', 99);
insert into my_test(student_id, class_id, score) values (2, 'c1', 90);
insert into my_test(student_id, class_id, score) values (2, 'c3', 87);
insert into my_test(student_id, class_id, score) values (3, 'c3', 99);
insert into my_test(student_id, class_id, score) values (3, 'c1', 90);
insert into my_test(student_id, class_id, score) values (3, 'c2', 87);
期望的輸出為：
1, 'c1', 99
2, 'c2', 99
3, 'c3', 99
***************
如果是單純的輸出 每種學生編號的最大分數(不列出class_id)以下sql即可達到
sql>
select student_id,max(score) from my_test group by student_id;
但現在是要求連class_id也要列出，因此以上的方法就會失敗
因此可透過ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 )達成
sql>
select tt.student_id,tt.class_id,tt.score from
(select m.*,ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score desc)rn from my_test m)tt
where rn=1;
分解說明:
select m.*,ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score desc)RN from my_test m;
透過ROW_NUMBER() OVER 以class_id分組然後指定欄位score降冪排序，因此可得到rn的排序編號
RN
1 c1 99 1
2 c1 90 2
3 c1 90 3
2 c2 99 1
1 c2 90 2
3 c2 87 3
3 c3 99 1
2 c3 87 2
1 c3 87 3
因此rn=1的都是score最高的，然後再透過子查詢將rn=1的值取出
案例2:
取出各種類型最近的三筆記錄
***********************
create table t_test(
id number primary key not null,
contract_type varchar2(10),
contract_name varchar2(20),
expire_date varchar2(10)
);
insert into t_test values(1,'A001','A类合同','2015-06-12');
insert into t_test values(2,'A001','A类合同','2015-06-11');
insert into t_test values(3,'A001','A类合同','2015-06-10');
insert into t_test values(4,'A001','A类合同','2015-06-09');
insert into t_test values(5,'B001','B类合同','2015-06-12');
insert into t_test values(6,'B001','B类合同','2015-06-11');
insert into t_test values(7,'B001','B类合同','2015-06-10');
insert into t_test values(8,'B001','B类合同','2015-06-09');
insert into t_test values(9,'C001','C类合同','2015-06-12');
insert into t_test values(10,'C001','C类合同','2015-06-11');
insert into t_test values(11,'C001','C类合同','2015-06-10');
insert into t_test values(12,'C001','C类合同','2015-06-09');
insert into t_test values(13,'C001','C类合同','2015-06-08');
解:
select contract_name,expire_date from (
select t.contract_name,t.expire_date, ROW_NUMBER() OVER (PARTITION BY contract_name ORDER BY expire_date desc )aa from t_test t
) where aa<=3;
