题解 | #统计复旦用户8月练题情况#

题目分为几个阶段，根据条件要求：

① 在user_profile表中找出属于复旦大学的相关用户信息，记为表一

select
    device_id,
    university
from user_profile
where university = "复旦大学"

② 在question_practice_detai表中找出属于8月份题目的相关信息，记为表二

select
    device_id,
    question_id,
    result,
    date
from question_practice_detail
where month(date) = 8

③ 使用left join将表一和表二合并（因为没有答题的同学也需要计入），记为表三

select
    up.device_id,
    university,
    question_id,
    result
from (select
         device_id,
         university
      from user_profile
      where university = "复旦大学") as up 
left join (select
               device_id,
               question_id,
               result,
               date
           from question_practice_detail
           where month(date) = 8) as qpd
on up.device_id = qpd.device_id 

④ 根据表三，使用group by对device_id进行分组，count函数统计question_id个数为总题目数；sum函数汇总即使用case when 条件选择答对的题目进行统计，即可获得最后结果

select
    device_id,
    university,
    count(question_id) as question_cnt,
    sum(case when result = "wrong" then 0
    	       when result IS NULL then 0
    	       else 1 end) as right_question_cnt
from (select
          up.device_id,
          university,
          question_id,
          result
      from (select
                device_id,
                university
            from user_profile
            where university = "复旦大学") as up 
left join (select
               device_id,
               question_id,
               result,
               date
           from question_practice_detail
           where month(date) = 8) as qpd
on up.device_id = qpd.device_id ) as t 
group by device_id, university