一个BI公司的面试题，有兴趣的朋友可以来做做一呀？
面试题（用oracle 或sql server 实现)
T_Score(分数表)
Stu_idLession_idScore
001L001 90
001L002 86
002L001 84
002L004 75
003L003 85
004L005 98
…..
T_Stu_Profile(学生表)
Stu_idStu_NameSexAgeClass_id
001郭东 F160611
002李西 M180612
003张北 F160613
004钱南 M170611
005王五 F170614
006赵七 F160615
……
T_Lession(课程表)
Lession_idLession_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
------解决方案--------------------
第二题
WITH A AS
(SELECT *
FROM (SELECT
T_LESSION.LESSION_NAME,
T_STU_PROFILE.STU_NAME || '+' || SCORE MZFS,
DENSE_RANK() OVER(PARTITION BY T_SCORE.LESSION_ID ORDER BY SCORE) MC
FROM T_SCORE,T_STU_PROFILE,T_LESSION
WHERE
T_LESSION.LESSION_ID=T_SCORE.LESSION_ID
AND
T_SCORE.STU_ID=T_STU_PROFILE.STU_ID)
WHERE MC < 4),
B AS
(SELECT LESSION_NAME,
DECODE(MC, '1', MZFS, '') F,
DECODE(MC, '2', MZFS, '') S,
DECODE(MC, '3', MZFS, '') T
FROM A)
SELECT T_LESSION.LESSION_NAME "科目",
(SELECT F
FROM B
WHERE T_LESSION.LESSION_NAME = B.LESSION_NAME
AND F IS NOT NULL) "第一",
(SELECT S
FROM B
WHERE T_LESSION.LESSION_NAME = B.LESSION_NAME
AND S IS NOT NULL) "第二",
(SELECT T
FROM B
WHERE T_LESSION.LESSION_NAME = B.LESSION_NAME
AND T IS NOT NULL) "第三"
FROM T_LESSION
[解决办法]
Select A.Stu_id,A.Stu_Name,A.Class_ID,Lession_NameFrom T_Stu_Profile ALeft Join T_Lession B On 1=1Left Join T_Score C On A.Stu_id = C.Stu_id AND B.Lession_id = C.Lession_idWhere C.Score is null
[解决办法]
一题：
select stu_name,class_id,lession_namefrom (select stu_id,stu_name,class_id,lession_id,lession_namefrom T_Stu_Profile,T_Lession) bwhere not exists(select 1 from t_score cwhere c.stu_id=b.stu_idand c.lession_id=b.lession_id);
