SQL查询强化训练(2)

摘自 http://www.iteye.com/topic/1112015

现在有三个表student,score,course
对应的建表语句如下：

CREATE TABLE student( 
student_id NUMBER PRIMARY KEY, 
student_name,VARCHAR2(30) NOT NULL) 

CREATE TABLE score( 
score_id NUMBER PRIMARY KEY, 
student_id NUMBER, 
course_id NUMBER, 
score NUMBER) 

CREATE TABLE course( 
course_id NUMBER PRIMARY KEY, 
course_name VARCHAR2(30)) 

要求用基本SQL实现如下的两条查询要求：
(1) 求出每门课程成绩排名前五名的同学的姓名，分数和课程名
(2)求出每门课程成绩排名第三的同学的姓名，分数和课程名

答案：

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;

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;

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;

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.<span style="color: rgb(0, 0, 0); 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>  
 
 结果如下：
 


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


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


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