一个BI公司的面试题，有兴趣的朋友可以来做做一呀？

Question

面试题（用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语句显示，最好不要使用游标与变量

Answer 1

1
select *
 from T_Lession a
 where 
     not exists
    (
     select 1
      from T_Score b
      where a.Lession_id=b.Lession_id
    )

Answer 2

2
select Lession_Name 课程,max(decode(rn,1,c.Stu_Name||a.Score),null) "第一名(姓名+分数)",
       max(decode(rn,2,c.Stu_Name||a.Score),null) "第二名(姓名+分数)",
       max(decode(rn,3,c.Stu_Name||a.Score),null) "第三名(姓名+分数)"     from
         (
           select a.Stu_id,a.Lession_id,a.Score ,b.Lession_Name ,c.Stu_Name
               row_number() over (partition by a.Lession_id order by Score  desc ) rn
               from T_Lession a,T_Lession b,T_Stu_Profile c
               where a.Lession_id=b.Lession_id
               and a.stu_id=c.stuid         )
where rn<=3
group by Lession_Name

Answer 3

3select c.Stu_Name 姓名 
        sum(decode(a.Lession_id,'L001',score,0)  语文,
       sum(decode(a.Lession_id,'L002',score,0)  数学,
       sum(decode(a.Lession_id,'L003',score,0)   英语,
       sum(decode(a.Lession_id,'L004',score,0)   物理,
       sum(decode(a.Lession_id,'L005',score,0)   化学,
       sum(score) 总分                
               from T_Lession a,T_Lession b,T_Stu_Profile c
               where a.Lession_id=b.Lession_id
               and a.stu_id=c.stuid
               and b.Class_id ='0611'
group by c.Stu_Name

Answer 4

1:   
SELECT DISTINCT 
       LESS1.STU_ID,
       SCORE.LESSION_ID,
       SCORE.LESSION_NAME
  FROM T_SESSION LESS1,
       T_SCORE SCORE
 WHERE SCORE.LESSION_ID NOT IN (
                                 SELECT LESS2.LESSION_ID
                                   FROM T_SESSION LESS2
                                  WHERE LESS1.STU_ID = LESS2.STU_ID
                                )
  ;或者用minus实现也可以2: 级别排位问题+max()3: 行变列(包括总分sum(...))

Answer 5

1.select b.stu_name Name, a.class_id Class, a.lession_id Lession
(select distinct stu_id, count(Lession_id) cnt_lessen, 
        max(class_id) class_id, max(Lession_id) lession_id 
 from T_Score 
 group by stu_id 
 having count(*) < ( select count(distinct Lession_id) from T_Lession )
) a  /* a means the set that has students not take part in exam.*/
join T_Stu_Profile b
on   a.stu_id     = b.stu_id
join T_Lession c
on   a.lession_id = c.lession_id
where b.stu_id is not NULL and
      c.lession_id is not NULL

Answer 6

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

Answer 7

3. 找出0611班所有人成绩，以下列格式显示
姓名 语文 数学 英语 物理 化学 总分 SQL> edit
已写入 file afiedt.buf  1  select b.stu_name,
  2         max(decode(a.lession_id, 'L001', a.score, null)) score1,
  3         max(decode(a.lession_id, 'L002', a.score, null)) score2,
  4         max(decode(a.lession_id, 'L003', a.score, null)) score3,
  5         max(decode(a.lession_id, 'L004', a.score, null)) score4,
  6         max(decode(a.lession_id, 'L005', a.score, null)) score5,
  7         sum(a.score) total_score
  8    from t_score a, t_stu_profile b
  9   where a.stu_id = b.stu_id
 10     and b.class_id = '0611'
 11*    group by b.stu_name
SQL> /STU_NAME       SCORE1     SCORE2     SCORE3     SCORE4     SCORE5 TOTAL_SCORE
---------- ---------- ---------- ---------- ---------- ---------- -----------
钱南                                                           98          98
郭冬               90         86                                          176

Answer 8

1. 写出学生没有参加考试的课程,以下形式显示
学生姓名 班级 课程题目意思是：比如001没考化学，数学，就需要找出
郭东 0611 化学
郭东 0611 数学 SQL> edit
已写入 file afiedt.buf  1  select a.stu_name, a.class_id, b.lession_name from t_stu_profile a, t_lessi
on b
  2  where not exists(select 1 from t_score c where a.stu_id||b.lession_id = c.s
tu_id||c.lession_id)
  3* order by a.stu_id, a.class_id
SQL> /STU_NAME   CLAS LESSION_NAME
---------- ---- --------------------
郭冬       0611 英语
郭冬       0611 化学
郭冬       0611 物理
李西       0612 化学
李西       0612 英语
李西       0612 数据
张北       0613 语文
张北       0613 物理
张北       0613 化学
张北       0613 数据
钱南       0611 英语STU_NAME   CLAS LESSION_NAME
---------- ---- --------------------
钱南       0611 数据
钱南       0611 语文
钱南       0611 物理
王五       0614 物理
王五       0614 化学
王五       0614 语文
王五       0614 数据
王五       0614 英语
赵七       0615 英语
赵七       0615 化学
赵七       0615 语文STU_NAME   CLAS LESSION_NAME
---------- ---- --------------------
赵七       0615 数据
赵七       0615 物理已选择24行。

Answer 9

应该是少了一个班级表，所以班级名称只能用班级ID 代替
第一题
WITH A AS 
(SELECT T.STU_ID,A.LESSION_ID FROM T_STU_PROFILE T,T_LESSION A)SELECT T_STU_PROFILE.STU_NAME,
       T_STU_PROFILE.CLASS_ID,
       T_LESSION.LESSION_NAME
  FROM A, T_STU_PROFILE, T_LESSION
 WHERE NOT EXISTS (SELECT F.STU_ID, F.LESSION_ID
          FROM T_SCORE F
         WHERE A.STU_ID = F.STU_ID
           AND A.LESSION_ID = F.LESSION_ID)
   AND A.STU_ID = T_STU_PROFILE.STU_ID
   AND A.LESSION_ID = T_LESSION.LESSION_ID

Answer 10

第二题
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

Answer 11

WITH A AS
(SELECT LESSION_NAME, STU_NAME, SCORE
  FROM T_LESSION, T_SCORE, T_STU_PROFILE
 WHERE T_STU_PROFILE.STU_ID = T_SCORE.STU_ID
   AND T_SCORE.LESSION_ID = T_LESSION.LESSION_ID
   AND T_STU_PROFILE.CLASS_ID = '0611'),
B AS
(SELECT T_LESSION.LESSION_NAME,
       STU_NAME,
       (SELECT SCORE
          FROM A
         WHERE T_LESSION.LESSION_NAME = A.LESSION_NAME
           AND T_STU_PROFILE.STU_NAME = A.STU_NAME)FS
  FROM T_LESSION, T_STU_PROFILE
 WHERE T_STU_PROFILE.CLASS_ID = '0611'),
C AS
(SELECT S.STU_NAME,
       DECODE(LESSION_NAME, '语文 ', FS, '') YWF,
       DECODE(LESSION_NAME, '数据 ', FS, '') SJF,
       DECODE(LESSION_NAME, '英语', FS, '') YYF,
       DECODE(LESSION_NAME, '物理 ', FS, '') WLF,
       DECODE(LESSION_NAME, '化学', FS, '') HXF,
       (SELECT SUM(FS) FROM B WHERE S.STU_NAME = B.STU_NAME) HJ
  FROM B S)SELECT 
T_STU_PROFILE.STU_NAME,
(SELECT YWF FROM C WHERE C.STU_NAME=T_STU_PROFILE.STU_NAME AND YWF IS NOT NULL)YWF,
(SELECT SJF FROM C WHERE C.STU_NAME=T_STU_PROFILE.STU_NAME AND SJF IS NOT NULL)SJF,
(SELECT YYF FROM C WHERE C.STU_NAME=T_STU_PROFILE.STU_NAME AND YYF IS NOT NULL)YYF,
(SELECT WLF FROM C WHERE C.STU_NAME=T_STU_PROFILE.STU_NAME AND WLF IS NOT NULL)WLF,
(SELECT HXF FROM C WHERE C.STU_NAME=T_STU_PROFILE.STU_NAME AND HXF IS NOT NULL)HXF,
(SELECT DISTINCT HJ FROM C WHERE C.STU_NAME=T_STU_PROFILE.STU_NAME)HJ
FROM
T_STU_PROFILE
WHERE
T_STU_PROFILE.CLASS_ID='0611'

Answer 12

12楼终于找到个正确的了。我来发个我的笨办法：
select t.stu_id, t.stu_name, t.lession_id, t.lession_name,p.score
       from (select a.stu_id, a.stu_name, b.lession_id, b.lession_name
               from T_Stu_Profile a, T_Lession b) t,
        T_Score p
        where t.stu_id = p.stu_id(+)
        and t.lession_id=p.lession_id(+)
        and p.score is null;

Answer 13

Select A.Stu_id,A.Stu_Name,A.Class_ID,Lession_Name
From T_Stu_Profile A
Left Join T_Lession B On 1=1
Left Join T_Score C 
On A.Stu_id = C.Stu_id AND B.Lession_id = C.Lession_id
Where C.Score is null001        郭东       0611       英语      
001        郭东       0611       物理      
001        郭东       0611       化学      
002        李西       0612       数据      
002        李西       0612       英语      
002        李西       0612       化学      
003        张北       0613       语文      
003        张北       0613       数据      
003        张北       0613       物理      
003        张北       0613       化学      
004        钱南       0611       语文      
004        钱南       0611       数据      
004        钱南       0611       英语      
004        钱南       0611       物理      
005        王五       0614       语文      
005        王五       0614       数据      
005        王五       0614       英语      
005        王五       0614       物理      
005        王五       0614       化学      
006        赵七       0615       语文      
006        赵七       0615       数据      
006        赵七       0615       英语      
006        赵七       0615       物理      
006        赵七       0615       化学      

Answer 14

问题3:
Select Stu_id,Sum(yw),
Sum(sx),Sum(yy),Sum(wl),Sum(hx) ,Sum(Score) 
from
(
Select A.Stu_id,
Case when Lession_Name = '语文' then ISNULL(Cast(Score as int),0) end  AS 'yw',
Case when Lession_Name = '数据' then ISNULL(Cast(Score as int),0) end  AS sx,  
Case when Lession_Name = '英语' then ISNULL(Cast(Score as int),0) end  AS yy,   
Case when Lession_Name = '物理' then ISNULL(Cast(Score as int),0) end  AS wl,
Case when Lession_Name = '化学' then ISNULL(Cast(Score as int),0) end  AS hx, 
ISNULL(Cast(Score as int),0) AS Score
From T_Stu_Profile A
Left Join T_Lession B On 1=1
Left Join T_Score C 
On A.Stu_id = C.Stu_id AND B.Lession_id = C.Lession_id
Where C.Score is not null
)B Group by Stu_id
001        90 86 NULL NULL NULL 176
002        84 NULL NULL 75 NULL 159
003        NULL NULL 85 NULL NULL 85
004        NULL NULL NULL NULL 98 98

Answer 15

一题：
select stu_name,class_id,lession_name
from 
(select stu_id,stu_name,class_id,lession_id,lession_name
from T_Stu_Profile,T_Lession) b
where not exists(select 1 from t_score c
where c.stu_id=b.stu_id
and c.lession_id=b.lession_id);二题：
select lession_name,
max(case when rn=1 then stu_name||score end) first,
max(case when rn=2 then stu_name||score end) second,
max(case when rn=3 then stu_name||score end) third
from
(select a.lession_name,c.stu_name,b.score,b.rn
from t_lession a,(
select stu_id,lession_id,score,row_number() over(partition by lession_id order by score desc) rn
from T_Score) b,
T_Stu_Profile c
where a.lession_id=b.lession_id
and c.stu_id=b.stu_id
and b.rn<=3) d
group by lession_name;三题：
select stu_name,sum(decode(lession_id,'L001',score,0)) "语文",
sum(decode(lession_id,'L002',score,0)) "数学",
sum(decode(lession_id,'L003',score,0)) "外语",
sum(decode(lession_id,'L004',score,0)) "物理",
sum(decode(lession_id,'L005',score,0)) "化学",
sum(score) "总分"
from 
(select stu_name,lession_id,score
from t_stu_profile a,t_score b
where a.stu_id=b.stu_id
and a.class_id='0611')
group by stu_name;

Answer 16

select f.stu_name, f.class_id, u.lession_name
  from (select substr(s_l, 1, instr(s_l, 'L') - 1) as stu_id,
               substr(s_l, instr(s_l, 'L'), 99) as lession_id
          from (select a.stu_id || b.lession_id as s_l
                  from jxy_0905_T_Stu_Profile a, jxy_0905_T_Lession b)
         where s_l not in
               (select stu_id || lession_id from jxy_0905_T_Score)) t,
       jxy_0905_T_Stu_Profile f,
       jxy_0905_T_Lession u
 where t.stu_id = f.stu_id
   and t.lession_id = u.lession_id;

Answer 17

第一题：
select a.stu_name as '学生姓名' ,a.class_id as '班级' ,b.lession_name as '课程' 
from t_stu_profile as a 
join t_lession as b
on 1=1
where not exists
(select 1 from t_score as c
where a.stu_id = c.stu_id and b.lession_id = c.Lession_id
)

Answer 18

第二题：
select lession_name as '课程' ,[1] as '第一名(姓名+分数)' ,[2] as '第二名(姓名+分数)' ,[3] '第三名(姓名+分数) '
from (
select b.stu_name + '+' + convert(char ,a.score) as score ,a.lession_id ,c.lession_name 
,RANK() over(partition by a.lession_id order by a.score) as rank_id
from t_score as a ,t_stu_profile as b ,t_lession as c
where a.stu_id = b.stu_id and a.lession_id = c.lession_id
) as Source
pivot(
max(score)
for rank_id in ([1] ,[2] ,[3])
) as P ;

Answer 19

第三题：
select stu_name as '姓名' ,L001 as '语文' ,L002 as '数据' ,L003 as '英语' ,L004 as '物理' ,L005 as '化学' 
,isnull([L001] ,0) +isnull([L002] ,0) +isnull([L003] ,0) +isnull([L004] ,0) +isnull([L005] ,0) as '总分'
from(
select b.stu_name ,a.lession_id ,a.score
from t_score as a
,t_stu_profile as b
where b.class_id = '0611'
and a.stu_id = b.stu_id
) as source
Pivot(
sum(score)
for lession_id in (L001 ,L002 ,L003 ,L004 ,L005)
) as P ;