一个做BI公司的数据库面试题，有兴趣的朋友可以来做了试下？有一定难度，可以来挑战下(50分)

面试题（用oracle 或sql server 实现)<br>T_Score(分数表)<br>Stu_id Lession_id &nbsp; &nbsp; &nbsp; &nbsp; Score<br>001 &nbsp;L001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 90<br>001 &nbsp;L002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 86<br>002 &nbsp;L001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 84<br>002 &nbsp;L004 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 75<br>003 &nbsp;L003 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 85<br>004 &nbsp;L005 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 98<br>….. <br><br>T_Stu_Profile(学生表)<br>Stu_id Stu_Name Sex Age Class_id<br>001 &nbsp; &nbsp;郭东 &nbsp;F &nbsp;16 &nbsp; &nbsp; 0611<br>002 &nbsp; &nbsp;李西 &nbsp;M &nbsp;18 &nbsp; &nbsp; 0612<br>003 &nbsp; &nbsp;张北 &nbsp;F &nbsp;16 &nbsp; &nbsp; 0613<br>004 &nbsp; &nbsp;钱南 &nbsp;M &nbsp;17 &nbsp; &nbsp; 0611<br>005 &nbsp; &nbsp;王五 &nbsp;F &nbsp;17 &nbsp; &nbsp; 0614<br>006 &nbsp; &nbsp;赵七 &nbsp;F &nbsp;16 &nbsp; &nbsp; 0615<br>…… <br><br>T_Lession(课程表)<br>Lession_id &nbsp;Lession_Name<br>L001 &nbsp; &nbsp; &nbsp; &nbsp;语文<br>L002 &nbsp; &nbsp; &nbsp; &nbsp;数据<br>L003 &nbsp; &nbsp; &nbsp; &nbsp;英语<br>L004 &nbsp; &nbsp; &nbsp; &nbsp;物理<br>L005 &nbsp; &nbsp; &nbsp; &nbsp;化学<br><br>1. 写出学生没有参加考试的课程,以下形式显示<br>学生姓名 班级 课程<br> <br> <br> <br> <br>以最简单SQL语句显示，最好不要使用游标与变量<br>2. 找出课程的前三名，以下列形式显示<br><br>课程 第一名(姓名+分数) 第二名(姓名+分数) 第三名(姓名+分数)<br>语文 <br>数学 <br>英语 <br>物理 <br>化学 <br> <br>以最简单SQL语句显示，最好不要使用游标与变量<br><br>3. 找出0611班所有人成绩，以下列格式显示<br>姓名 语文 数学 英语 物理 化学 总分<br> <br> <br> <br> <br>以最简单SQL语句显示，最好不要使用游标与变量

第1题和第3题都可以做出来<br>但第2题不怎么样才能用一个SQL查出来。

第二题,其实很不难,关键是你要会用子查询,<br>关键代码如下<br>select *<br>(select T_Stu_Profile.Stu_Name,T_Lession.Lession_name,T_Score.Score,max.tmd from<br>(select &nbsp;Liession_id,Score,(select count(*) from T_Score as xx <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Where xx.Liession=yy.Liession <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;And &nbsp; xx.Score&gt;=yy.Score) as tmd<br>From(select distinct Liession_id,Score From T_Score) yy<br>)max3 <br>Left join T_Score on max3.Lession_id=T_Score.Lession_id &nbsp;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and &nbsp;max3.Score=T_Score.Score<br>Left join T_Lession on T_Score.Lession_id=T_Lession.Lession_id<br>Left join T_Stu_Profile on T_Score.stu_id=T_Stu_Profile.Stu_id<br>Where Max3.tmd&lt;=3<br>)a<br><br><br>然后再对它进行行列转换就可以了

认真拜读了mokmok兄弟写的这段代码，但你这段代码好像每科的前三名都没有找出来。<br>更别说把它横向转纵向了。

不用游标确实有点难,怎么确定学科来组合SQL语句

如果学科不定,难<br>听课

oracle有分析函数rank，如果用标准SQL，其效率相当低下，并且对于分数相等的情况，结果是不一致的：<br>SQL&gt; select * from ascore;<br><br>STUID LESSONID &nbsp; &nbsp; &nbsp;SCORE<br>----- -------- ----------<br>001 &nbsp; L001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 90<br>002 &nbsp; L002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 86<br>002 &nbsp; L001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 84<br>002 &nbsp; L004 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 75<br>003 &nbsp; L001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 85<br>004 &nbsp; L001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 98<br>004 &nbsp; L002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60<br>004 &nbsp; L004 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 75<br><br>8 rows selected<br><br>SQL&gt; <br>SQL&gt; select * from<br>&nbsp; 2 &nbsp;(select rank()over(partition by lessonid order by score desc) n,stuid,lessonid,score from ascore)<br>&nbsp; 3 &nbsp;where n&lt;=3 order by lessonid,n;<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;N STUID LESSONID &nbsp; &nbsp; &nbsp;SCORE<br>---------- ----- -------- ----------<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 004 &nbsp; L001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 98<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 001 &nbsp; L001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 90<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 003 &nbsp; L001 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 85<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 002 &nbsp; L002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 86<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 004 &nbsp; L002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 60<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 002 &nbsp; L004 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 75<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 004 &nbsp; L004 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 75<br><br>7 rows selected<br><br>SQL&gt; <br>SQL&gt; select * from (select count(*) n,t1.stuid,t1.lessonid from ascore t1,ascore t2 where<br>&nbsp; 2 &nbsp;t1.lessonid=t2.lessonid and t1.score&lt;=t2.score<br>&nbsp; 3 &nbsp;group by (t1.stuid,t1.lessonid) having (count(*)&lt;=3))<br>&nbsp; 4 &nbsp;order by lessonid,n;<br><br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;N STUID LESSONID<br>---------- ----- --------<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 004 &nbsp; L001<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 001 &nbsp; L001<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3 003 &nbsp; L001<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1 002 &nbsp; L002<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 004 &nbsp; L002<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 004 &nbsp; L004<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 002 &nbsp; L004<br><br>7 rows selected

行列转换相信大家都会，就不罗嗦了

不用游标,高手出手三

在线等了，我看了很是难啊，高手解决一下

大家都可以来试下呀，用oralce写，会简单一些，不用sql server 写的话，就有难度了。

胡乱写了一个，呵呵<br>1 select a.Stu_Name 学生姓名, a.Class_id 班级, b.Lession_Name 课程 from &nbsp; T_Stu_Profile a, T_Lession b<br>&nbsp; where b.Lession_id not in(select Lession_id from T_Score c where c.Stu_id = a.Stu_id)<br>&nbsp; order by a.Stu_id<br><br>2 select a.Lession_Name 课程, e.Stu_Name + cast(b.Score as varchar(3)) 第一名, f.Stu_Name + cast(c.Score as varchar(3)) 第二名, <br>&nbsp; &nbsp; g.Stu_Name + cast(d.Score as varchar(3)) 第三名 <br>&nbsp; from T_Lession a<br>&nbsp; left join T_Score b on b.Lession_id = a.Lession_id and b.Score =<br>&nbsp; &nbsp; (select max(Score) Score from T_Score where T_Score.Lession_id = a.Lession_id)<br>&nbsp; left join T_Score c on c.Lession_id = a.Lession_id and c.Score =<br>&nbsp; &nbsp; (select max(Score) Score from T_Score where T_Score.Lession_id = a.Lession_id and T_Score.Score &lt;&gt; b.Score)<br>&nbsp; left join T_Score d on d.Lession_id = a.Lession_id and d.Score =<br>&nbsp; &nbsp; (select max(Score) Score from T_Score where T_Score.Lession_id = a.Lession_id and T_Score.Score &lt;&gt; b.Score and T_Score.Score &lt;&gt; c.Score)<br>&nbsp; left join T_Stu_Profile e on e.Stu_id = b.Stu_id<br>&nbsp; left join T_Stu_Profile f on f.Stu_id = c.Stu_id<br>&nbsp; left join T_Stu_Profile g on g.Stu_id = d.Stu_id<br><br>3 select a.Stu_Name 姓名, b.Score 语文, c.Score 数学, d.Score 英语, e.Score 物理, f.Score 化学,<br>&nbsp; &nbsp; isnull(b.Score, 0) + isnull(c.Score, 0) + isnull(d.Score, 0) + isnull(e.Score, 0) + isnull(f.Score, 0) 总分<br>&nbsp; from T_Stu_Profile a<br>&nbsp; left join T_Score b on b.Lession_id = 'L001' and b.Stu_id = a.Stu_id<br>&nbsp; left join T_Score c on c.Lession_id = 'L002' and c.Stu_id = a.Stu_id<br>&nbsp; left join T_Score d on d.Lession_id = 'L003' and d.Stu_id = a.Stu_id<br>&nbsp; left join T_Score e on e.Lession_id = 'L004' and e.Stu_id = a.Stu_id<br>&nbsp; left join T_Score f on f.Lession_id = 'L005' and f.Stu_id = a.Stu_id<br>&nbsp; where a.Class_id = '0611'

楼上的兄弟还真是说老实话<br>呵呵,不知道你运行过没

你运行试一试不就知道了吗[]，sqlserver2000数据库

to lzh1983:<br>&nbsp; &nbsp;运行了,行!真的好强哦!

SQL怎么才有效率测试太难了。建议取消SQL。整个使用接口操纵数据。

第一个题目, 我想是考mssql 的 in 查询 只能用单主键的问题,我的设计是, 假设学生编号是固定长度(如果不固定就转换成固定长度),同样课程编号也是这样处理。然后组合成一个单键,用in 子查询。如下：<br>select * &nbsp;from T_Stu_Profile,t_lession <br>where Stu_id + Lession_id not in (<br>select Stu_id + Lession_id &nbsp; from T_Score where Stu_id + Lession_id )<br><br>这个是没有转换的例子，转换长度的就略过了。

第一题:select * from <br>(select test.*,d.score from &nbsp;(select t_stu_profile.*,t_Lession.lession_id,lession_name from t_stu_profile,t_lession) as test &nbsp; left join t_score d<br>&nbsp;on (d.stu_id=test.stu_id and d.lession_id=test.lession_id)) f where f.score is null<br>其余两题不做行列转换的等有时间再贴出

第二题,修改哈前面一个兄弟的<br>select * from<br>(select T_Stu_Profile.Stu_Name,T_Lession.Lession_name,T_Score.Score,max3.tmd from<br>(select &nbsp;Lession_id,Score,(select count(*) from T_Score as xx <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Where xx.Lession_id=yy.Lession_id <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;And &nbsp; xx.Score&gt;=yy.Score) as tmd<br>From(select distinct Lession_id,Score From T_Score) yy<br>)max3 <br>Left join T_Score on max3.Lession_id=T_Score.Lession_id &nbsp;<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; and &nbsp;max3.Score=T_Score.Score<br>Left join T_Lession on T_Score.Lession_id=T_Lession.Lession_id<br>Left join T_Stu_Profile on T_Score.stu_id=T_Stu_Profile.Stu_id<br>Where Max3.tmd&lt;=3<br>)a

第三题我也只能做到这<br>select c.stu_name,sum(case a.lession_id when '100' then score else 0 end ) as 语文 ,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(case a.lession_id when '101' then score else 0 end ) as 数据 ,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(case a.lession_id when '102' then score else 0 end ) as 英语 ,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(case a.lession_id when '103' then score else 0 end ) as 物理 ,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sum(case a.lession_id when '104' then score else 0 end ) as 化学 <br>&nbsp;from &nbsp;t_stu_profile c left join t_score a on a.stu_id=c.stu_id left join t_lession b on a.lession_id=b.lession_id <br><br>&nbsp;where class_id='0611' group by c.stu_name,b.lession_name<br>Oracle的应该要好办些,有Loop end 对于不定列的就好办<br><br>Oracle的完全解决方案,稍候如果有时间的话我会给出