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

select a1.device_id,a1.university, IFNULL(a2.question_cnt,0),IFNULL(right_question_cnt,0)
#IFNULL函数将join后的null值变成0
from user_profile as a1
left join
(select device_id,count(device_id) as question_cnt,
sum(case when result='right' then 1 else 0 end ) as right_question_cnt
from question_practice_detail
where month(date)=8
group by device_id)as a2
on a1.device_id=a2.device_id
where a1.university='复旦大学'