面试题（用oracle 或sql server 实现)
T_Score(分数表)
Stu_id Lession_id Score
001 L001 90
001 L002 86
002 L001 84
002 L004 75
003 L003 85
004 L005 98
…..
T_Stu_Profile(学生表)
Stu_id Stu_Name Sex Age Class_id
001 郭东 F 16 0611
002 李西 M 18 0612
003 张北 F 16 0613
004 钱南 M 17 0611
005 王五 F 17 0614
006 赵七 F 16 0615
……
T_Lession(课程表)
Lession_id Lession_Name
L001 语文
L002 数据
L003 英语
L004 物理
L005 化学
1. 写出学生没有参加考试的课程,以下形式显示
学生姓名 班级 课程
以最简单SQL语句显示，最好不要使用游标与变量
2. 找出课程的前三名，以下列形式显示
课程 第一名(姓名+分数) 第二名(姓名+分数) 第三名(姓名+分数)
语文
数学
英语
物理
化学
以最简单SQL语句显示，最好不要使用游标与变量
3. 找出0611班所有人成绩，以下列格式显示
姓名 语文 数学 英语 物理 化学 总分
以最简单SQL语句显示，最好不要使用游标与变量
------解决方案--------------------
2. 找出课程的前三名，以下列形式显示
课程 第一名(姓名+分数) 第二名(姓名+分数) 第三名(姓名+分数)
语文
数学
英语
物理
化学
SQL> select *from t_lession
2 ;
LESS LESSION_NAME
---- --------------------
L001 语文
L002 数据
L003 英语
L004 物理
L005 化学
SQL> select *from t_stu_profile;
STU STU_NAME S AGE CLAS
--- ---------- - ---------- ----
001 郭冬 F 16 0611
002 李西 M 18 0612
003 张北 F 16 0613
004 钱南 M 17 0611
005 王五 F 17 0614
006 赵七 F 16 0615
已选择6行。
SQL> select *from t_score;
STU LESS SCORE
--- ---- ----------
001 L001 90
001 L002 86
002 L001 84
002 L004 75
003 L003 85
004 L005 98
已选择6行。
SQL> edit
已写入 file afiedt.buf
1 select b.lession_name,
2 (select c.stu_name
3 from t_stu_profile c
4 where a.highest_stu_id = c.stu_id) || ' ' || a.highest_score highe
st_stu_score,
5 (select c.stu_name
6 from t_stu_profile c
7 where a.second_stu_id = c.stu_id) || ' ' || a.second_score second_
stu_score,
8 (select c.stu_name
9 from t_stu_profile c
10 where a.third_stu_id = c.stu_id) || ' ' || a.third_score third_stu
_score
11 from t_lession b,
12 (select lession_id,
13 max(decode(num, 1, stu_id, null)) highest_stu_id,
14 max(decode(num, 1, score, null)) highest_score,
15 max(decode(num, 2, stu_id, null)) second_stu_id,
16 max(decode(num, 2, score, null)) second_score,
17 max(decode(num, 3, stu_id, null)) third_stu_id,
18 max(decode(num, 3, score, null)) third_score
19 from (select stu_id,
20 lession_id,
21 score,
22 row_number() over(partition by lession_id order by s
core desc) as num
23 from t_score)
24 where num <= 3
25 group by lession_id) a
26* where b.lession_id = a.lession_id
SQL> /
LESSION_NAME HIGHEST_STU_ SECOND_STU_S THIRD_STU_SC
-------------------- ------------ ------------ ------------
语文 郭冬 90 李西 84
英语 张北 85
物理 李西 75
化学 钱南 98
数据 郭冬 86
------解决方案--------------------