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

[necyhk]

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

[leosming]

关注中

[mantisXF]

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(...))

[rexyudl]

应该是少了一个班级表，所以班级名称只能用班级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

[rexyudl]

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

[rexyudl]

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'

[necyhk]

楼上写的不错

[xiaoxiao1984]

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

[xiaoxiao1984]

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行。

[Polanco]

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;

[binyun530]

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 null

001 郭东 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 化学

[binyun530]

一定要给我分哦

[binyun530]

问题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

[zjkk1011]

应该是lesson吧

[vc555]

一题：

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;

[gz442060097]

哇，好大答案呀！

[kukuxiaolang]

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;

[hxm1207]

第一题：
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
)

[hxm1207]

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

[hxm1207]

第三题：
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 ;

[kdjceoajcy2dc]

[Quote=引用 25 楼 的回复:]

第二题：
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……
[/Quote]
你的方法给了我很大的提示，但是有个问题，假如有两个人分数并列第一，只会显示其中一个