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

select 
u.device_id,
u.university,
sum(case when result is not null then 1
else 0 end)  as question_cnt,
sum(case when result = 'right' then 1
else 0 end ) as right_question_cnt
from user_profile u left join question_practice_detail q
on u.device_id = q.device_id 
where
u.university  = '复旦大学'
and 
(month (q.`date`) = 08 or month (q.`date`) is null )
group by u.university,u.device_id

首先捞出复旦大学在08答题或者未作答的用户，注意不要遗漏null;然后根据用户号和学校分组后再使用casewhen sum统计78月份答题总数和正确答题数。