摘自
现在有三个表student,score,course
对应的建表语句如下：
View Code 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)求出每门课程成绩排名第三的同学的姓名，分数和课程名答案：
View Code 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;
View Code 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;
View Code 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；
View Code 1 结果如下： 2 3 4 5 6 STUDENT_NAME SCORE COURSE_NAME DRANK 7 张三 99 语文 1 8 李四 99 语文 1 9 王五 96 语文 210 马六 93 语文 311 孙七 90 语文 412 王八 87 语文 513 张三 98 数学 114 李四 97 数学 215 王五 95 数学 316 马六 92 数学 417 孙七 89 数学 518 李四 98 英语 119 张三 97 英语 220 王五 94 英语 321 马六 91 英语 422 23 孙七 88 英语 524 25
View Code 1 1.2成绩相同的人排名相同，且排名不是连续的。 2 Sql如下： 3 4 5 6 Sql代码 7 1.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; 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.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; 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.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; 94 95 结果如下： 96 97 98 99 STUDENT_NAME SCORE COURSE_NAME RN100 王五 96 语文 3101 王五 95 数学 3102 王五 94 英语 3