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

jinaqu

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

这只是实际当中需要用到的一个缩影，数据量想必不会小

jinaqu

peter1166 发表于 2014-12-24 15:24
这个SQL能插入到上表 ， 自己试过么？

我是先插数据，然后倒出来的insert语句，没执行过

stilllovekk

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

版主，minus不是比not exists效率更高吗？为什么看到很多人都用not exits而不用minus？求指导

恩墨学院

这面试题…………岂能难倒我们技术人

拿不动25

---第一题

select
        st.stu_name,
        class_id   ,
        lession_name
from
        t_lession le,
        t_stu_profile st
where
        le.lession_id not in
        (
                select lession_id from t_score sc where sc.stu_id = st.stu_id
        )
order by
        1,3;

---第二题
select * from (
select
        le.lession_name,
        st.stu_name,
        rank() over (partition by le.lession_name order by score desc) rk ,
        score
from
        t_lession le,
        t_score sc  ,
        t_stu_profile st
where
        le.lession_id = sc.lession_id
    and sc.stu_id     = st.stu_id) as p
    pivot (max(stu_name || '+' ||score) for  p.rk in (1 as "第一名",2 as "第二名",3 as "第三名")  ) as t;

---第三题
select * from (
select stu_name,lession_name,score, sum(score) over(partition by sc.stu_id)  as "总分"
  from T_STU_PROFILE stu,T_SCORE sc,T_LESSION le
where stu.stu_id = sc.stu_id
   and sc.lession_id = le.lession_id
   and stu.class_id = '0611') as p
   pivot (max(score) for p.lession_name in ('语文', '数学', '英语','物理','化学' ) )  

KBmohei

SELECT STU_NAME, CLASS_ID, LESSION_NAME
FROM (
  SELECT stu.STU_ID, stu.STU_NAME, stu.CLASS_ID, les.LESSION_ID, les.LESSION_NAME
  FROM t_stu_profile stu, t_lession les
)
where (STU_ID, LESSION_ID) not in (select STU_ID, LESSION_ID from t_score)

KBmohei

SELECT les.LESSION_NAME, MAX(CASE WHEN rank = 1 THEN t.STU_NAME || ': ' || t.score ELSE NULL END) AS firstRank
  , MAX(CASE WHEN rank = 2 THEN t.STU_NAME || ': ' || t.score ELSE NULL END) AS secondRank
  , max(case when rank = 3 then t.STU_NAME || ': ' || t.score else null end) as thirdRank
FROM t_lession les left join (
  SELECT stu.STU_NAME, LESSION_ID, score, rank() OVER(PARTITION BY LESSION_ID ORDER BY score DESC) AS rank
  FROM t_score s, t_stu_profile stu
  WHERE stu.STU_ID = s.STU_ID
) t ON les.LESSION_ID = t.LESSION_ID
group by les.LESSION_NAME

KBmohei

SELECT *
FROM (
  SELECT stu.STU_NAME, les.LESSION_NAME, s.SCORE
  FROM t_lession les LEFT JOIN t_score s ON les.LESSION_ID = s.LESSION_ID
  LEFT JOIN t_stu_profile stu ON stu.CLASS_ID = '0611' AND stu.STU_ID = s.STU_ID
) pivot(
  max(score) for LESSION_NAME in ('语文', '数据', '英语', '物理', '化学')
)
where STU_NAME is not null