select up.device_id,
       university,
  		//是八月份计数+1（从0开始）,不是计数+0
       sum(if(month(qpd.date) = 8,1,0)) as question_cnt,
  		//答题结果正确计数+1，错误+0（同上）
  sum(if(qpd.result = 'right',1,0)) as right_question_cnt
from user_profile up
//左表全部列出，右表数据补0
left join question_practice_detail qpd
on up.device_id = qpd.device_id 

where up.university = '复旦大学'//注意：只在where后面限定时间会丢失一条数据（没有答题的用户）
group by up.device_id