Oracle的over() 函数使用


表及数据： Sql代码
  1. 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;  
Sql代码
  1. 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;
Sql代码
  1. 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;

(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名：
根据不同的排名方式有三种不同的sql写法：
1.1成绩相同的人排名相同，且排名是连续的。
Sql如下：

Sql代码
  1. 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; 

 

结果如下：

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. 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;  
结果如下：
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. 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;  

 结果如下：

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. 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;  

 结果如下：

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

相关内容