oracle分析函数

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;

　　

 

oracle分析函数

标签：