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

〇〇 · 发表于 2014-12-21 21:04

jinaqu 发表于 2014-12-21 11:27
“有表财务表，数据如下
year Profit
2007 1000000000

你是怎么写的

jinaqu · 发表于 2014-12-21 21:38

时间有限，就随便拼在一起了，应该是直接-

kf_panda · 发表于 2014-12-23 11:24

select Stu_Name,Class_id,lession_Name
from T_Stu_Profile a,T_Lession b
where not exists
(select 1 from T_Score c where a.Stu_id=c.Stu_id and b.Stu_id=c.Lession_id );
不知道有没有更好的写法，这样写如果学生表数据量大，应该也不快吧

chengwanhe · 发表于 2014-12-23 14:47

jinaqu 发表于 2014-12-18 18:37
帮忙搞搞看，我没搞出来

搞出来比较麻烦，有没有简便的，11g 行列互转也可以用 pivot和unpivo

demo@gavindb>l
1 SELECT r.lession_name,
2 MAX(DECODE(r.rank,1,r.top,NULL)) top1,
3 MAX(DECODE(r.rank,2,r.top,NULL)) top2,
4 MAX(DECODE(r.rank,3,r.top,NULL)) top3
5 FROM (
6 WITH p AS(
7 SELECT lession_id,
8 score,
9 stu_id,
10 RANK() OVER(PARTITION BY lession_id ORDER BY score DESC) rank
11 FROM t_score
12 GROUP BY lession_id,score,stu_id
13 )
14 SELECT m.lession_id,m.lession_name,n.stu_name ||'+'|| u.score top,u.rank
15 FROM t_lession m,t_stu_profile n,p u
16 WHERE m.lession_id = u.lession_id
17 AND n.stu_id = u.stu_id
18 ORDER BY u.lession_id,u.rank
19 ) r
20* GROUP BY r.lession_name
demo@gavindb>/
LESSION_NAME TOP1 TOP2 TOP3
--------------- --------------- --------------- ---------------
数据郭东+86
物理李西+75
化学钱南+98
英语张北+85
语文郭东+90 李西+84

复制代码

mailghyth · 发表于 2014-12-23 17:24

本帖最后由 mailghyth 于 2014-12-23 17:24 编辑

第二题
WITH AA AS (
SELECT C.LESSION_NAME,B.STU_NAME,A.SCORE
FROM T_SCORE A,T_STU_PROFILE B,T_LESSION C
WHERE A.STU_ID=B.STU_ID AND A.LESSION_ID=C.LESSION_ID ORDER BY C.LESSION_NAME
)SELECT LESSION_NAME,
MAX(DECODE(SRANK,1,STU_NAME||SCORE)),
MAX(DECODE(SRANK,2,STU_NAME||SCORE)),
MAX(DECODE(SRANK,3,STU_NAME||SCORE))
FROM (SELECT LESSION_NAME,STU_NAME,SCORE,RANK() OVER(PARTITION BY LESSION_NAME ORDER BY SCORE DESC) SRANK FROM AA) D
GROUP BY LESSION_NAME

nuaaguang · 发表于 2014-12-24 09:07

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

select s.lession_name,
   (select qiansan
      from (select t.lession_id,
                     r.stu_name || '+' || t.score qiansan,
                     rank() over(partition by t.lession_id order by t.score desc) ranks
               from T_Score t, T_Stu_Profile r
               where r.stu_id = t.stu_id)
      where ranks = 1
         and lession_id = s.lession_id) 第一名，
            (select qiansan
      from (select t.lession_id,
                     r.stu_name || '+' || t.score qiansan,
                     rank() over(partition by t.lession_id order by t.score desc) ranks
               from T_Score t, T_Stu_Profile r
               where r.stu_id = t.stu_id)
      where ranks = 2
         and lession_id = s.lession_id) 第二名，
            (select qiansan
      from (select t.lession_id,
                     r.stu_name || '+' || t.score qiansan,
                     rank() over(partition by t.lession_id order by t.score desc) ranks
               from T_Score t, T_Stu_Profile r
               where r.stu_id = t.stu_id)
      where ranks = 3
         and lession_id = s.lession_id) 第三名
  from T_Lession s