select t1.device_id,t1.university,
count(t2.question_id) as question_cnt,
sum(case when result='right' then 1 else 0 end) as right_question_cnt
from user_profile t1
left join question_practice_detail t2
using (device_id)
where university= '复旦大学' and (MONTH(date)=8 or date is null)
group by device_id

重点不要漏掉没有答题的用户