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

#t1表计算所有device_id的8月的question_cnt，right_question_cnt
#t2表计算复旦大学的device_id
#用device_id连接t1和t2，device_id没连到的t2表的question_cnt和right_question_cnt通过ifnull设置为0



select t2.device_id,university,ifnull(question_cnt,0),ifnull(right_question_cnt,0)
from
(select device_id,count(question_id) as question_cnt,
sum(case when result='right' then 1 else 0 end) as right_question_cnt
from question_practice_detail 
where substr(date,1,7)='2021-08'
group by device_id)t1
right join 
(select device_id,university from user_profile where university='复旦大学')t2
on t1.device_id=t2.device_id