select u1.device_id, university, sum(case when month(date)=8 then 1 else 0 end) question_cnt, sum(case when result ='right' then 1 else 0 end) right_question_cnt from user_profile u1 left join question_practice_detail qpd on qpd.device_id = u1.device_id where university = '复旦大学' group by u1.device_...