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

select
    up.device_id,
    university,
    count(qpd.question_id) as question_cnt,
    sum(if(qpd.result='right',1,0)) as right_question_cnt

from
    user_profile as up
left join question_practice_detail as qpd
on up.device_id = qpd.device_id and month(date)='08'

where university='复旦大学'  #and month(date)='08'如果加在这里的话，由于4321没有记录，返回的是null而非0
group by up.device_id