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

select m.device_id,m.university,if(n.question_cnt is null,0,n.question_cnt) as question_cnt,if(n.right_question_cn is null,0,n.right_question_cn)as right_question_cnt
from
(select distinct device_id,university
from
user_profile
where university='复旦大学')m
left join
(select c.device_id,count(c.question_id) as question_cnt,sum(if (c.result='wrong',0,1))right_question_cn
from
(select a.device_id,a.question_id,a.result,a.date,b.university
from
question_practice_detail a
join
user_profile b
on a.device_id=b.device_id
and b.university='复旦大学' and month(a.date)=8)c
group by c.device_id)n
on m.device_id=n.device_id