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

iscogeek

解题思路：先取T_Stu_Profile(学生表) 与T_Lession(课程表) 的笛卡尔积，
然后再与T_Score(分数表) 通过stu_id(学号)与lession_id(课程号)外关联，
去掉有分数的记录，剩下的就是学生没有参加考试的课程了。
--
select a.stu_name
       ,a.class_id
       ,a.lession_name
  from (
select *
  from T_Stu_Profile a
       ,T_Lession    c
       ) a
       ,t_score b
where 1 = 1
   and a.lession_id = b.lession_id(+)
   and a.stu_id     = b.stu_id(+)
   and b.lession_id is null
order by a.stu_id,a.lession_id ;

skiiwujinhua

SELECT *
  FROM (SELECT S.STU_ID,s.stu_name, T.LESSION_ID,t.lession_name FROM T_STU_PROFILE S, T_LESSION T) S
  LEFT JOIN T_SCORE T
    ON S.STU_ID = T.STU_ID
   AND S.LESSION_ID = T.LESSION_ID
WHERE t.STU_ID IS NULL
   AND t.LESSION_ID IS NULL

iscogeek

第二题 。。。
--
select c.lession_name
       ,e.stu_score1
       ,e.stu_score2
       ,e.stu_score3                           
  from T_Lession c
       ,(select d.lession_id
                ,max(decode(d.rn,1,d.stu_id||'+'||d.score)) stu_score1
                ,max(decode(d.rn,2,d.stu_id||'+'||d.score)) stu_score2
                ,max(decode(d.rn,3,d.stu_id||'+'||d.score)) stu_score3                           
           from  (select a.stu_id
                         ,a.stu_name
                         ,b.lession_id
                         ,b.score
                         ,row_number() over (partition by b.lession_id order by b.score desc ) rn
                    from T_Stu_Profile a
                         ,t_score      b
                   where 1 = 1
                     and a.stu_id = b.stu_id
                  )       d
           where 1 = 1
             and rn <= 3
           group by d.lession_id
        )        e
where 1 = 1
   and c.lession_id = e.lession_id(+)
order by c.lession_name ;

jinaqu

都是厉害的主

jinaqu

iscogeek 发表于 2014-12-18 23:25
解题思路：先取T_Stu_Profile(学生表) 与T_Lession(课程表) 的笛卡尔积，
然后再与T_Score(分数表) 通过st ...

这个方法很赞

newkid

如果已知每个学生都参加了至少一门考试，即成绩表中包含了所有的学生，那么这个笛卡尔积可以省掉，用带PARTITION的OUTER JOIN。

师傅叫我来入世

SELECT *
  FROM T_SCORE T1, T_STU_PROFILE T2, T_LESSION T3
WHERE T1.STU_ID = T2.STU_ID
   AND T1.LESSION_ID = T3.LESSION_ID
   AND T1.SCORE IS NULL;
  不知道对不对，没测试，，如果想知道某个学生没有参加考试的那门课程，那就说明那个学生的那门课程成绩为空了，，，

师傅叫我来入世

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

版主，select  1到底好处在哪？求解

Skye_to_Skye

那家公司

iyee_tu

师傅叫我来入世 发表于 2014-12-19 09:32
SELECT *
  FROM T_SCORE T1, T_STU_PROFILE T2, T_LESSION T3
WHERE T1.STU_ID = T2.STU_ID

不对.........