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

select u.device_id,
       u.university,
       sum(if(qpd.result is not null,1,0)) as question_cnt,   #使用if函数最简洁
       sum(if(qpd.result ='right',1,0)) as right_question_cnt
from user_profile as u 
join question_practice_detail as qpd 
on u.device_id= qpd.device_id

where   university='复旦大学' and`date` like '2021-08%'
group by u.device_id #按用户聚集