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

SELECT u.device_id ,u.university,case when all_q is null then 0 else all_q end as question_cnt , case when (r_q is null) then 0 else r_q end as right_question_cnt  from (SELECT device_id,COUNT(question_id) as all_q from question_practice_detail  where  MONTH(date)=8 GROUP BY device_id) as q1 left JOIN  (SELECT device_id,COUNT(question_id) as r_q from question_practice_detail  where  MONTH(date)=8 AND result='right'  GROUP BY device_id) as q2 ON q1.device_id = q2.device_id  right join 
user_profile as u on u.device_id = q1.device_id WHERE u.university = '复旦大学'

由于没有想到用sum 所以就把question_practice_detail进行了自联结，找出了要的错题和正确的提，然后右连接用户表 最后再处理一下null值，虽然过了 但是效率有点低 向前面老哥学习