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

select 
        a.device_id,
        a.university,
        count(question_id) as question_cnt,
        sum(if(result='right',1,0)) as right_question_cnt  -- 也可以count搭配case when
from
    (-- 筛选出复旦大学的
     select 
        device_id, 
        university 
    from user_profile 
    where university='复旦大学') a
left join 
    (-- 筛选出8月份答题的
    select 
        device_id,
        question_id,
        result
    from question_practice_detail
    where month(date)=8 ) b
on a.device_id=b.device_id
group by a.device_id, a.university