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

-- 这竟然是困不敢相信。。。
select 
 device_id,max(university) university,
 count(case when month(date)=8 then 1 else null end) question_cnt,
 sum(case when month(date)=8 and result='right' then 1 else 0 end ) as right_question_cnt
from  
(select u.*,q.question_id,q.result,q.date 
from  user_profile u 
left join question_practice_detail q 
using(device_id)
where u.university = '复旦大学') t
group by device_id