使用分析函数来为记录排名的问题
[size=large]现在有三个表student,score,course
对应的建表语句如下：
CREATE TABLE student(
student_id NUMBER PRIMARY KEY,
student_name,VARCHAR2(30) NOT NULL)
CREATE TABLE score(
score_id NUMBER PRIMARY KEY,
student_id NUMBER,
course_id NUMBER,
score NUMBER)
CREATE TABLE course(
course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(30))
要求用基本SQL实现如下的两条查询要求：
(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名
(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名
Oracle的解法：
表及数据：
Sql代码
<SPAN style="FONT-SIZE: large">create table STUDENT
(
STUDENT_ID NUMBER not null,
STUDENT_NAME VARCHAR2(30) not null
)
;
alter table STUDENT
add primary key (STUDENT_ID);
prompt Loading STUDENT...
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (1, '张三');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (2, '李四');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (3, '王五');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (4, '马六');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (5, '孙七');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (6, '王八');
commit;</SPAN>
create table STUDENT
(
STUDENT_ID NUMBER not null,
STUDENT_NAME VARCHAR2(30) not null
)
;
alter table STUDENT
add primary key (STUDENT_ID);
prompt Loading STUDENT...
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (1, '张三');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (2, '李四');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (3, '王五');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (4, '马六');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (5, '孙七');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (6, '王八');
commit; Sql代码
<SPAN style="FONT-SIZE: large">create table COURSE
(
COURSE_ID NUMBER not null,
COURSE_NAME VARCHAR2(30)
)
;
alter table COURSE
add primary key (COURSE_ID);
prompt Loading COURSE...
insert into COURSE (COURSE_ID, COURSE_NAME)
values (1, '语文');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (2, '数学');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (3, '英语');
commit;</SPAN>
create table COURSE
(
COURSE_ID NUMBER not null,
COURSE_NAME VARCHAR2(30)
)
;
alter table COURSE
add primary key (COURSE_ID);
prompt Loading COURSE...
insert into COURSE (COURSE_ID, COURSE_NAME)
values (1, '语文');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (2, '数学');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (3, '英语');
commit; Sql代码
<SPAN style="FONT-SIZE: large">create table SCORE
(
SCORE_ID NUMBER not null,
STUDENT_ID NUMBER,
COURSE_ID NUMBER,
SCORE NUMBER
)
;
alter table SCORE
add primary key (SCORE_ID);
prompt Loading SCORE...
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (1, 1, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (2, 1, 2, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (3, 1, 3, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (4, 2, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (5, 2, 2, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (6, 2, 3, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (7, 3, 1, 96);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (8, 3, 2, 95);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (9, 3, 3, 94);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (10, 4, 1, 93);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (11, 4, 2, 92);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (12, 4, 3, 91);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (13, 5, 1, 90);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (14, 5, 2, 89);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (15, 5, 3, 88);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (16, 6, 1, 87);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (17, 6, 2, 86);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (18, 6, 3, 85);
commit;</SPAN>
create table SCORE
(
SCORE_ID NUMBER not null,
STUDENT_ID NUMBER,
COURSE_ID NUMBER,
SCORE NUMBER
)
;
alter table SCORE
add primary key (SCORE_ID);
prompt Loading SCORE...
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (1, 1, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (2, 1, 2, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (3, 1, 3, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (4, 2, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (5, 2, 2, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (6, 2, 3, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (7, 3, 1, 96);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (8, 3, 2, 95);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (9, 3, 3, 94);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (10, 4, 1, 93);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (11, 4, 2, 92);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (12, 4, 3, 91);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (13, 5, 1, 90);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (14, 5, 2, 89);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (15, 5, 3, 88);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (16, 6, 1, 87);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (17, 6, 2, 86);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (18, 6, 3, 85);
commit;
(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名：
根据不同的排名方式有三种不同的sql写法：
1.1成绩相同的人排名相同，且排名是连续的。
Sql如下：
Sql代码
<SPAN style="FONT-SIZE: large">select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
dense_rank() over(partition by c.COURSE_ID order by sc.SCORE desc) drank
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.drank < 6;</SPAN>
select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
dense_rank() over(partition by c.COURSE_ID order by sc.SCORE desc) drank
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.drank < 6; 结果如下：
STUDENT_NAME SCORE COURSE_NAME DRANK
张三 99 语文 1
李四 99 语文 1
王五 96 语文 2
马六 93 语文 3
孙七 90 语文 4
王八 87 语文 5
张三 98 数学 1
李四 97 数学 2
王五 95 数学 3
马六 92 数学 4
孙七 89 数学 5
李四 98 英语 1
张三 97 英语 2
王五 94 英语 3
马六 91 英语 4
孙七 88 英语 5
1.2成绩相同的人排名相同，且排名不是连续的。
Sql如下：
Sql代码
<SPAN style="FONT-SIZE: large; COLOR: #000000">select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.ranking < 6;</SPAN>
select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.ranking < 6; 结果如下：
STUDENT_NAME SCORE COURSE_NAME RANKING
张三 99 语文 1
李四 99 语文 1
王五 96 语文 3
马六 93 语文 4
孙七 90 语文 5
张三 98 数学 1
李四 97 数学 2
王五 95 数学 3
马六 92 数学 4
孙七 89 数学 5
李四 98 英语 1
张三 97 英语 2
王五 94 英语 3
马六 91 英语 4
孙七 88 英语 5
1.2成绩相同的人根据学号排序，排名是连续的。
Sql如下：
Sql代码
<SPAN style="FONT-SIZE: large">select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.rn < 6;</SPAN>
select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.rn < 6; 结果如下：
STUDENT_NAME SCORE COURSE_NAME RN
张三 99 语文 1
李四 99 语文 2
王五 96 语文 3
马六 93 语文 4
孙七 90 语文 5
张三 98 数学 1
李四 97 数学 2
王五 95 数学 3
马六 92 数学 4
孙七 89 数学 5
李四 98 英语 1
张三 97 英语 2
王五 94 英语 3
马六 91 英语 4
孙七 88 英语 5
(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名：
Sql如下：
Sql代码
<SPAN style="FONT-SIZE: large">select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.rn = 3;</SPAN>
select *
from (select s.STUDENT_NAME,
sc.SCORE,
c.COURSE_NAME,
row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
from student s, course c, score sc
where s.STUDENT_ID = sc.STUDENT_ID
and c.COURSE_ID = sc.COURSE_ID) t
where t.rn = 3; 结果如下：
STUDENT_NAME SCORE COURSE_NAME RN
王五 96 语文 3
王五 95 数学 3
王五 94 英语 3
[/size]
Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。
①ROW_NUMBER：
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
②DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
③RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_rank.html
http://www.iteye.com/job/topic/1112015
