摘自 http://www.iteye.com/topic/1112015
现在有三个表student,score,course
对应的建表语句如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 CREATE TABLE student( 2 student_id NUMBER PRIMARY KEY, 3 student_name,VARCHAR2(30) NOT NULL) 4 5 CREATE TABLE score( 6 score_id NUMBER PRIMARY KEY, 7 student_id NUMBER, 8 course_id NUMBER, 9 score NUMBER) 10 11 CREATE TABLE course( 12 course_id NUMBER PRIMARY KEY, 13 course_name VARCHAR2(30))
要求用基本SQL实现如下的两条查询要求:
(1) 求出每门课程成绩排名前五名的同学的姓名,分数和课程名
(2)求出每门课程成绩排名第三的同学的姓名,分数和课程名
答案:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 create table STUDENT 2 2.( 3 3. STUDENT_ID NUMBER not null, 4 4. STUDENT_NAME VARCHAR2(30) not null 5 5.) 6 6.; 7 7.alter table STUDENT 8 8. add primary key (STUDENT_ID); 9 9. 10 10.prompt Loading STUDENT... 11 11.insert into STUDENT (STUDENT_ID, STUDENT_NAME) 12 12.values (1, '张三'); 13 13.insert into STUDENT (STUDENT_ID, STUDENT_NAME) 14 14.values (2, '李四'); 15 15.insert into STUDENT (STUDENT_ID, STUDENT_NAME) 16 16.values (3, '王五'); 17 17.insert into STUDENT (STUDENT_ID, STUDENT_NAME) 18 18.values (4, '马六'); 19 19.insert into STUDENT (STUDENT_ID, STUDENT_NAME) 20 20.values (5, '孙七'); 21 21.insert into STUDENT (STUDENT_ID, STUDENT_NAME) 22 22.values (6, '王八'); 23 23.commit;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 create table COURSE 2 2.( 3 3. COURSE_ID NUMBER not null, 4 4. COURSE_NAME VARCHAR2(30) 5 5.) 6 6.; 7 7.alter table COURSE 8 8. add primary key (COURSE_ID); 9 9. 10 10.prompt Loading COURSE... 11 11.insert into COURSE (COURSE_ID, COURSE_NAME) 12 12.values (1, '语文'); 13 13.insert into COURSE (COURSE_ID, COURSE_NAME) 14 14.values (2, '数学'); 15 15.insert into COURSE (COURSE_ID, COURSE_NAME) 16 16.values (3, '英语'); 17 17.commit;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 create table SCORE 2 2.( 3 3. SCORE_ID NUMBER not null, 4 4. STUDENT_ID NUMBER, 5 5. COURSE_ID NUMBER, 6 6. SCORE NUMBER 7 7.) 8 8.; 9 9.alter table SCORE 10 10. add primary key (SCORE_ID); 11 11. 12 12.prompt Loading SCORE... 13 13.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 14 14.values (1, 1, 1, 99); 15 15.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 16 16.values (2, 1, 2, 98); 17 17.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 18 18.values (3, 1, 3, 97); 19 19.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 20 20.values (4, 2, 1, 99); 21 21.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 22 22.values (5, 2, 2, 97); 23 23.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 24 24.values (6, 2, 3, 98); 25 25.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 26 26.values (7, 3, 1, 96); 27 27.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 28 28.values (8, 3, 2, 95); 29 29.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 30 30.values (9, 3, 3, 94); 31 31.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 32 32.values (10, 4, 1, 93); 33 33.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 34 34.values (11, 4, 2, 92); 35 35.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 36 36.values (12, 4, 3, 91); 37 37.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 38 38.values (13, 5, 1, 90); 39 39.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 40 40.values (14, 5, 2, 89); 41 41.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 42 42.values (15, 5, 3, 88); 43 43.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 44 44.values (16, 6, 1, 87); 45 45.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 46 46.values (17, 6, 2, 86); 47 47.insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE) 48 48.values (18, 6, 3, 85); 49 49.commit;
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;
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 结果如下: 2 3 4 5 6 STUDENT_NAME SCORE COURSE_NAME DRANK 7 张三 99 语文 1 8 李四 99 语文 1 9 王五 96 语文 2 10 马六 93 语文 3 11 孙七 90 语文 4 12 王八 87 语文 5 13 张三 98 数学 1 14 李四 97 数学 2 15 王五 95 数学 3 16 马六 92 数学 4 17 孙七 89 数学 5 18 李四 98 英语 1 19 张三 97 英语 2 20 王五 94 英语 3 21 马六 91 英语 4 22 23 孙七 88 英语 5 24 25
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1 1.2成绩相同的人排名相同,且排名不是连续的。 2 Sql如下: 3 4 5 6 Sql代码 7 1.<span style="color: rgb(0, 0, 0); font-size: large;">select * 8 2. from (select s.STUDENT_NAME, 9 3. sc.SCORE, 10 4. c.COURSE_NAME, 11 5. rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking 12 6. from student s, course c, score sc 13 7. where s.STUDENT_ID = sc.STUDENT_ID 14 8. and c.COURSE_ID = sc.COURSE_ID) t 15 9.where t.ranking < 6;</span> 16 17 结果如下: 18 19 20 21 STUDENT_NAME SCORE COURSE_NAME RANKING 22 张三 99 语文 1 23 李四 99 语文 1 24 王五 96 语文 3 25 马六 93 语文 4 26 孙七 90 语文 5 27 张三 98 数学 1 28 李四 97 数学 2 29 王五 95 数学 3 30 马六 92 数学 4 31 孙七 89 数学 5 32 李四 98 英语 1 33 张三 97 英语 2 34 王五 94 英语 3 35 马六 91 英语 4 36 孙七 88 英语 5 37 38 39 1.2成绩相同的人根据学号排序,排名是连续的。 40 Sql如下: 41 42 43 44 Sql代码 45 1.<span style="font-size: large;">select * 46 2. from (select s.STUDENT_NAME, 47 3. sc.SCORE, 48 4. c.COURSE_NAME, 49 5. row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn 50 6. from student s, course c, score sc 51 7. where s.STUDENT_ID = sc.STUDENT_ID 52 8. and c.COURSE_ID = sc.COURSE_ID) t 53 9.where t.rn < 6;</span> 54 55 结果如下: 56 57 58 59 STUDENT_NAME SCORE COURSE_NAME RN 60 张三 99 语文 1 61 李四 99 语文 2 62 王五 96 语文 3 63 马六 93 语文 4 64 孙七 90 语文 5 65 张三 98 数学 1 66 李四 97 数学 2 67 王五 95 数学 3 68 马六 92 数学 4 69 孙七 89 数学 5 70 李四 98 英语 1 71 张三 97 英语 2 72 王五 94 英语 3 73 马六 91 英语 4 74 孙七 88 英语 5 75 76 77 78 79 (2)求出每门课程成绩排名第三的同学的姓名,分数和课程名: 80 Sql如下: 81 82 83 84 Sql代码 85 1.<span style="font-size: large;">select * 86 2. from (select s.STUDENT_NAME, 87 3. sc.SCORE, 88 4. c.COURSE_NAME, 89 5. row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn 90 6. from student s, course c, score sc 91 7. where s.STUDENT_ID = sc.STUDENT_ID 92 8. and c.COURSE_ID = sc.COURSE_ID) t 93 9.where t.rn = 3;</span> 94 95 结果如下: 96 97 98 99 STUDENT_NAME SCORE COURSE_NAME RN 100 王五 96 语文 3 101 王五 95 数学 3 102 王五 94 英语 3