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

select
    a.device_id,
    a.university,
    count(b.question_id) as question_cnt,
    sum(if(b.result = 'right',1,0))  as right_question_cnt
from
    (
        select
            device_id,
            university
        from
            user_profile
        where
            university = '复旦大学'
    ) as a
    left join (
        select
            device_id,
            question_id,
            result,month(date)
        from
            question_practice_detail
        where  month(date)="8"
    ) as b on a.device_id = b.device_id
  group by device_id,university