招聘频道 → 招聘论坛
阅读 28561 次
主题：某企业SQL面试题求解
  • cumtlu
发表时间：2011-07-09
现在有三个表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)求出每门课程成绩排名第三的同学的姓名，分数和课程名

小弟愚昧 ，想了很长时间也没想出来，望各位大神不吝赐教帮小弟写出对应的SQL
  • treemap
发表时间：2011-07-09
去 csdn oracle版问问吧
  • jsjmz
发表时间：2011-07-09
数据库不同，可能写法不同，但是都是一个问题——分组排序
请 Google
e.g. oracle 分组排序
或者 oracle 分析函数
或者 sql 分组排序
等等
要学会知道问题的所在。
  • cumtlu
发表时间：2011-07-09
jsjmz 写道
数据库不同，可能写法不同，但是都是一个问题——分组排序
请 Google
e.g. oracle 分组排序
或者 oracle 分析函数
或者 sql 分组排序
等等
要学会知道问题的所在。

假定是oracle ，另外最好不要用特殊的函数，实现通用性
  • jsjmz
发表时间：2011-07-09
cumtlu 写道
jsjmz 写道
数据库不同，可能写法不同，但是都是一个问题——分组排序
请 Google
e.g. oracle 分组排序
或者 oracle 分析函数
或者 sql 分组排序
等等
要学会知道问题的所在。

假定是oracle ，另外最好不要用特殊的函数，实现通用性

实际上来说，一个项目在开始的时候，就已经考虑使用什么数据库了，而且一般来说，数据库不太会换（除非升级，比如mysql 升级 到 oracle），所以，能够使用一些 Oracle 特定的函数来更好地解决问题，不见得就是不好的，当然，如果你想使用标准的sql，为了标准和通用性，那就找相关标准的sql了。
  • javaOak
发表时间：2011-07-10

Oracle的解法：
表及数据：

create table STUDENT
(
  STUDENT_ID   NUMBER not null,
  STUDENT_NAME VARCHAR2(30) not null
)
;
alter table STUDENT
  add primary key (STUDENT_ID);

prompt Loading STUDENT...
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (1, '张三');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (2, '李四');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (3, '王五');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (4, '马六');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (5, '孙七');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (6, '王八');
commit;
  
create table COURSE
(
  COURSE_ID   NUMBER not null,
  COURSE_NAME VARCHAR2(30)
)
;
alter table COURSE
  add primary key (COURSE_ID);

prompt Loading COURSE...
insert into COURSE (COURSE_ID, COURSE_NAME)
values (1, '语文');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (2, '数学');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (3, '英语');
commit;
  
create table SCORE
(
  SCORE_ID   NUMBER not null,
  STUDENT_ID NUMBER,
  COURSE_ID  NUMBER,
  SCORE      NUMBER
)
;
alter table SCORE
  add primary key (SCORE_ID);

prompt Loading SCORE...
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (1, 1, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (2, 1, 2, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (3, 1, 3, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (4, 2, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (5, 2, 2, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (6, 2, 3, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (7, 3, 1, 96);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (8, 3, 2, 95);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (9, 3, 3, 94);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (10, 4, 1, 93);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (11, 4, 2, 92);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (12, 4, 3, 91);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (13, 5, 1, 90);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (14, 5, 2, 89);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (15, 5, 3, 88);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (16, 6, 1, 87);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (17, 6, 2, 86);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (18, 6, 3, 85);
commit;

 

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

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               dense_rank() over(partition by c.COURSE_ID order by sc.SCORE desc) drank
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
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如下：

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
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如下：

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
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如下：

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
where t.rn = 3;

 结果如下：

STUDENT_NAME SCORE COURSE_NAME RN
王五 96 语文 3
王五 95 数学 3
王五 94 英语 3
  • cumtlu
发表时间：2011-07-10
javaOak 写道

Oracle的解法：
表及数据：

create table STUDENT
(
  STUDENT_ID   NUMBER not null,
  STUDENT_NAME VARCHAR2(30) not null
)
;
alter table STUDENT
  add primary key (STUDENT_ID);

prompt Loading STUDENT...
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (1, '张三');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (2, '李四');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (3, '王五');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (4, '马六');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (5, '孙七');
insert into STUDENT (STUDENT_ID, STUDENT_NAME)
values (6, '王八');
commit;
  
create table COURSE
(
  COURSE_ID   NUMBER not null,
  COURSE_NAME VARCHAR2(30)
)
;
alter table COURSE
  add primary key (COURSE_ID);

prompt Loading COURSE...
insert into COURSE (COURSE_ID, COURSE_NAME)
values (1, '语文');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (2, '数学');
insert into COURSE (COURSE_ID, COURSE_NAME)
values (3, '英语');
commit;
  
create table SCORE
(
  SCORE_ID   NUMBER not null,
  STUDENT_ID NUMBER,
  COURSE_ID  NUMBER,
  SCORE      NUMBER
)
;
alter table SCORE
  add primary key (SCORE_ID);

prompt Loading SCORE...
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (1, 1, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (2, 1, 2, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (3, 1, 3, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (4, 2, 1, 99);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (5, 2, 2, 97);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (6, 2, 3, 98);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (7, 3, 1, 96);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (8, 3, 2, 95);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (9, 3, 3, 94);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (10, 4, 1, 93);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (11, 4, 2, 92);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (12, 4, 3, 91);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (13, 5, 1, 90);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (14, 5, 2, 89);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (15, 5, 3, 88);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (16, 6, 1, 87);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (17, 6, 2, 86);
insert into SCORE (SCORE_ID, STUDENT_ID, COURSE_ID, SCORE)
values (18, 6, 3, 85);
commit;

 

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

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               dense_rank() over(partition by c.COURSE_ID order by sc.SCORE desc) drank
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
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如下：

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               rank() over(partition by c.COURSE_ID order by sc.SCORE desc) ranking
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
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如下：

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
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如下：

select *
  from (select s.STUDENT_NAME,
               sc.SCORE,
               c.COURSE_NAME,
               row_number() over(partition by c.COURSE_ID order by sc.SCORE desc, s.STUDENT_ID) rn
          from student s, course c, score sc
         where s.STUDENT_ID = sc.STUDENT_ID
           and c.COURSE_ID = sc.COURSE_ID) t
where t.rn = 3;

 结果如下：

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


谢谢OAK兄，果然很强大哈，就是不知有没有通用性更好的呢？
  • csdxqzp
发表时间：2011-07-10
可以参见Oracle自带的函数

分析函数
select e.ename , e.job ,e.empno , e.mgr , e.deptno , e.sal , row_number() over(partition by e.deptno order by e.sal desc)
from emp e //连续

select e.ename , e.job ,e.empno , e.mgr , e.deptno , e.sal , rank() over(partition by e.deptno order by e.sal desc)
from emp e //跳跃

select e.ename , e.job ,e.empno , e.mgr , e.deptno , e.sal , dense_rank() over(partition by e.deptno order by e.sal desc)
from emp e //不跳跃
  • Edison_King
发表时间：2011-07-10

求前五名
select s.student_name , c.course_name , temp.score from student s , course c , (select score.student_id, score.course_id ,score.score , row_number() over(partition by course_id  order by score  desc) rn from score) temp where  temp.rn<=5 and s.student_id = temp.student_id and c.course_id = temp.course_id


求第三名
select s.student_name , c.course_name , temp.score from student s , course c , (select score.student_id, score.course_id ,score.score , row_number() over(partition by course_id  order by score  desc) rn from score) temp where  temp.rn=3 and s.student_id = temp.student_id and c.course_id = temp.course_id
  • qingming.com
发表时间：2011-07-10
--使用rownum来处理，大概思路：
1.按课程和成绩降序并编号
2.在1的基础上按课程分组，并求出每门课最小的编号
3.用1跟2关联，条件1.编号<2.编号+5就是取前5名;条件1.编号=2.编号+3就是第三名。
参考：http://www.iteye.com/problems/67654
Global site tag (gtag.js) - Google Analytics