oracle分析函数
浏览数：21 /
时间：2015年06月12日
一、应用场景
学生成绩表：
create table s_score ( id number(16) primary key not null, studentId number(16) not null, subject varchar(32) not null, test_time date default sysdate not null, score number(16,2) ); comment on table s_score is ‘学生分数‘; comment on column s_score.id is ‘记录id‘; comment on column s_score.studentId is ‘学生id‘; comment on column s_score.subject is ‘科目‘; comment on column s_score.test_time is ‘考试日期‘; comment on column s_score.score is ‘得分‘; create sequence s_score_seq minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 cache 20;
数据如下：
insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,1,‘A‘,sysdate,90); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,2,‘A‘,sysdate,90); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,3,‘A‘,sysdate,90); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,4,‘A‘,sysdate,92); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,5,‘A‘,sysdate,87); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,6,‘A‘,sysdate,80); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,7,‘A‘,sysdate,89); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,1,‘B‘,sysdate,70); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,2,‘B‘,sysdate,50); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,3,‘B‘,sysdate,60); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,4,‘B‘,sysdate,90); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,5,‘B‘,sysdate,90); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,6,‘B‘,sysdate,40); insert into s_score(id,studentid,subject,test_time,score) values(s_score_seq.nextval,7,‘B‘,sysdate,80);
二、排序函数rank() over(order by column) | dense_rank() over(order by column)
1.不分组
讲解：
排序有连续性：dense_rank() over(order by column)，例如：1、2、2、3、3、3、4
排序无连续性：rank() over(order by column)，例如：1、2、2、4、4、4、4、8
查询A学科下所有学生成绩排序，从高到低：注：order by默认按照升序排列，desc是降序即从高到低
dense_rank()over(order by column)：
select dense_rank() over(order by score desc) ranknum,s.studentid,s.score from s_score s where s.subject = ‘A‘;
rank()over(order by column):
select s.subject,rank() over(order by score desc) ranknum,s.studentid,s.score from s_score s where s.subject = ‘A‘;
2.分组排序
应用：查询A/B学科的前3名
select * from ( select s.subject,dense_rank() over(partition by s.subject order by s.score desc) ranknum,s.studentid,s.score from s_score s) where ranknum <= 3;
三、ROW_NUMBER
select s.subject,row_number() over(partition by s.subject order by s.score desc) ranknum,s.studentid,s.score from s_score s;
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
