Welcome 微信登录

首页 / 数据库 / MySQL / 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
Oracle学习笔记：SQL查询总结Oracle in与exists的选择相关资讯      Oracle教程 
  • Oracle中纯数字的varchar2类型和  （07/29/2015 07:20:43）
  • Oracle教程：Oracle中查看DBLink密  （07/29/2015 07:16:55）
  • [Oracle] SQL*Loader 详细使用教程  （08/11/2013 21:30:36）
  • Oracle教程：Oracle中kill死锁进程  （07/29/2015 07:18:28）
  • Oracle教程：ORA-25153 临时表空间  （07/29/2015 07:13:37）
  • Oracle教程之管理安全和资源  （04/08/2013 11:39:32）
本文评论 查看全部评论 （0）
表情： 姓名： 字数