oracle 经典分数排名

Oracle的解法：
表及数据：

Sql代码

1. <SPAN style="FONT-SIZE: large">create table STUDENT   
2. (   
3.   STUDENT_ID   NUMBER not null,   
4.   STUDENT_NAME VARCHAR2(30) not null  
5. )   
6. ;   
7. alter table STUDENT   
8.   add primary key (STUDENT_ID);   
9.   
10. prompt Loading STUDENT...   
11. insert into STUDENT (STUDENT_ID, STUDENT_NAME)   
12. values (1, '张三');   
13. insert into STUDENT (STUDENT_ID, STUDENT_NAME)   
14. values (2, '李四');   
15. insert into STUDENT (STUDENT_ID, STUDENT_NAME)   
16. values (3, '王五');   
17. insert into STUDENT (STUDENT_ID, STUDENT_NAME)   
18. values (4, '马六');   
19. insert into STUDENT (STUDENT_ID, STUDENT_NAME)   
20. values (5, '孙七');   
21. insert into STUDENT (STUDENT_ID, STUDENT_NAME)   
22. values (6, '王八');   
23. 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代码

1. <SPAN style="FONT-SIZE: large">create table COURSE   
2. (   
3.   COURSE_ID   NUMBER not null,   
4.   COURSE_NAME VARCHAR2(30)   
5. )   
6. ;   
7. alter table COURSE   
8.   add primary key (COURSE_ID);   
9.   
10. prompt Loading COURSE...   
11. insert into COURSE (COURSE_ID, COURSE_NAME)   
12. values (1, '语文');   
13. insert into COURSE (COURSE_ID, COURSE_NAME)   
14. values (2, '数学');   
15. insert into COURSE (COURSE_ID, COURSE_NAME)   
16. values (3, '英语');   
17. commit;</SPAN>  

create table COURSE ( COURSE_ID NUMBER not null, COURSE_NAME VARCHAR2(30) ) ; alter table COURSE ad  primary ke  (COURSE_ID);
p  p  oading COURSE...
in er  into COURSE (COURSE_ID, COURSE_NAME)
va ue  (1, '语文');
in er  into COURSE (COURSE_ID, COURSE_NAME)
va ue  (2, '数学');
in er  into COURSE (COURSE_ID, COURSE_NAME)
va ue  (3, '英语');
co mit; 

 

Sql代码

1. <SPAN style="FONT-SIZE: large">create table SCORE   
2. (   
3.   SCORE_ID   NUMBER not null,   
4.   STUDENT_ID NUMBER,   
5.   COURSE_ID  NUMBER,   
6.   SCORE      NUMBER   
7. )   
8. ;   
9. alter table SCORE   
10.   add primary key (SCORE_ID);   
11.   
12. prompt Loading SCORE...   
13. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
14. values (1, 1, 1, 99);   
15. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
16. values (2, 1, 2, 98);   
17. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
18. values (3, 1, 3, 97);   
19. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
20. values (4, 2, 1, 99);   
21. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
22. values (5, 2, 2, 97);   
23. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
24. values (6, 2, 3, 98);   
25. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
26. values (7, 3, 1, 96);   
27. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
28. values (8, 3, 2, 95);   
29. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
30. values (9, 3, 3, 94);   
31. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
32. values (10, 4, 1, 93);   
33. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
34. values (11, 4, 2, 92);   
35. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
36. values (12, 4, 3, 91);   
37. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
38. values (13, 5, 1, 90);   
39. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
40. values (14, 5, 2, 89);   
41. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
42. values (15, 5, 3, 88);   
43. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
44. values (16, 6, 1, 87);   
45. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
46. values (17, 6, 2, 86);   
47. insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)   
48. values (18, 6, 3, 85);   
49. 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代码

1. <SPAN style="FONT-SIZE: large">select *   
2.   from (select s.STUDENT_NAME,   
3.                sc.SCORE,   
4.                c.COURSE_NAME,   
5.                dense_rank() over(partition by c.COURSE_ID order by sc.SCORE desc) drank   
6.           from student s, course c, score sc   
7.          where s.STUDENT_ID = sc.STUDENT_ID   
8.            and c.COURSE_ID = sc.COURSE_ID) t   
9. 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代码

1. <SPAN style="COLOR: #000000; FONT-SIZE: large">select *   
2.   from (select s.STUDENT_NAME,   
3.                sc.SCORE,   
4.                c.COURSE_NAME,   
5.                rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking   
6.           from student s, course c, score sc   
7.          where s.STUDENT_ID = sc.STUDENT_ID   
8.            and c.COURSE_ID = sc.COURSE_ID) t   
9. 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代码

1. <SPAN style="FONT-SIZE: large">select *   
2.   from (select s.STUDENT_NAME,   
3.                sc.SCORE,   
4.                c.COURSE_NAME,   
5.                row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn   
6.           from student s, course c, score sc   
7.          where s.STUDENT_ID = sc.STUDENT_ID   
8.            and c.COURSE_ID = sc.COURSE_ID) t   
9. 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代码

1. <SPAN style="FONT-SIZE: large">select *   
2.   from (select s.STUDENT_NAME,   
3.                sc.SCORE,   
4.                c.COURSE_NAME,   
5.                row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn   
6.           from student s, course c, score sc   
7.          where s.STUDENT_ID = sc.STUDENT_ID   
8.            and c.COURSE_ID = sc.COURSE_ID) t   
9. 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

STUDENT_NAME SCORE COURSE_NAME RN 王五 96 语文 3 王五 95 数学 3 王五 94 英语 3