【讨论】大公司面试题，测测你的SQL水平

〇〇

哪家公司

Naldonado

dingjun123 发表于 2014-12-18 18:23
select a.stu_name,a.class_id,b.lession_name
from t_stu_profile a,t_lession b
where not exists

我也是这么想的。

Naldonado

jinaqu 发表于 2014-12-18 18:26
B.2.        找出课程的前三名，以下列形式显示
课程        第一名(姓名+分数)        第二名(姓名+分数)        第三名(姓名+分数)
...

你这结果基本上已经出来了啊。。。。你把你的结果再group by一下，第一名-----第三名的列取个max就好了。

然后如果你不能用分析函数的话，硬上sql的话，就
select t1.*,(select count(*)+1 from t_score t2 where T2.LESSION_ID=t1.lession_id and t2.score>t1.score)rank from t_score t1 )
select lession_id from tmp01

jinaqu

我目前还在琢磨第二个，在网上有看到大神贴的代码，甚是佩服，我这里分享给大家，多提意见哈!!

1. 
   
2. SELECT b.lession_name "课程",
   
3.        (SELECT c.stu_name
   
4.          FROM t_stu_profile c
   
5.          WHERE a.highest_stu_id = c.stu_id) || ' ' || a.highest_score "第一名(姓名+分数)",
   
6.        (SELECT c.stu_name
   
7.          FROM t_stu_profile c
   
8.          WHERE a.second_stu_id = c.stu_id) || ' ' || a.second_score "第二名(姓名+分数)",
   
9.        (SELECT c.stu_name
   
10.          FROM t_stu_profile c
    
11.          WHERE a.third_stu_id = c.stu_id) || ' ' || a.third_score "第三名(姓名+分数)"
    
12. FROM t_lession b,
    
13.      (SELECT lession_id, 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, lession_id, score,
    
20.                      row_number() over(PARTITION BY lession_id ORDER BY score DESC) AS num
    
21.               FROM t_score)
    
22.        WHERE num <= 3
    
23.        GROUP BY lession_id) a
    
24. WHERE b.lession_id = a.lession_id;
    

复制代码

jinaqu

Naldonado 发表于 2014-12-18 21:32
你这结果基本上已经出来了啊。。。。你把你的结果再group by一下，第一名-----第三名的列取个max就好了。 ...

厉害太感谢了

oracle_cj

不会。。。。

jinaqu

经过上面大神指点之后，完全对上了，哈哈，太感谢了

1. select  r.lession_name "课程",max(case r.srank when 1 then r.stu_name||'+'||r.score end) "第一名(姓名+分数)",max(case r.srank when 2 then r.stu_name||'+'||r.score end) "第二名(姓名+分数)",max(case r.srank when 3 then r.stu_name||'+'||r.score end) "第三名(姓名+分数)" from (select p.stu_name,s.score ,l.lession_name,rank() over(partition by l.lession_id order by s.score desc) srank from t_lession l,t_stu_profile p,t_score s
   
2. where s.lession_id=l.lession_id and s.stu_id=p.stu_id) r where r.srank<4
   
3. group by r.lession_name;

复制代码

jinaqu

3).找出0611班所有人成绩，以下列格式显示    姓名  语文  数学  英语  物理  化学  总分
    以最简单SQL语句显示，最好不要使用游标与变量

1. select info.stu_name "姓名",max(decode(info.lession_id,'L001',info.score)) "语文",max(decode(info.lession_id,'L002',info.score)) "数据",max(decode(info.lession_id,'L003',info.score)) "英语",max(decode(info.lession_id,'L004',info.score)) "物理",max(decode(info.lession_id,'L005',info.score)) "化学" from (select p.stu_name,s.score,l.lession_id from t_stu_profile p,t_score s,t_lession l where l.lession_id=s.lession_id and s.stu_id=p.stu_id and p.class_id=0611) info
   
2.     group by info.stu_name;

复制代码

jinaqu

上面少了个总分，加一个sum就行了

1. select info.stu_name "姓名",max(decode(info.lession_id,'L001',info.score)) "语文",max(decode(info.lession_id,'L002',info.score)) "数据",max(decode(info.lession_id,'L003',info.score)) "英语",max(decode(info.lession_id,'L004',info.score)) "物理",max(decode(info.lession_id,'L005',info.score)) "化学",sum(info.score) "总分" from (select p.stu_name,s.score,l.lession_id from t_stu_profile p,t_score s,t_lession l where l.lession_id=s.lession_id and s.stu_id=p.stu_id and p.class_id=0611) info
   
2.     group by info.stu_name;

复制代码

jinaqu

一共有8个SQL题，我只记得这3个，还是在网上找到相同的，放上来的，后面看看还有没线索找到其他剩余的题