题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
select a.device_id as device_id ,b.university as university ,count(1) as question_cnt ,sum(if(a.result = 'right',1,0)) as right_question_cnt from question_practice_detail a left join user_profile b on a.device_id = b.device_id where b.university = '复旦大学' and left(a.date,7) ='2021-08' group by 1 union all select a.device_id as device_id ,a.university as university ,0 as question_cnt ,0 as right_question_cnt from user_profile a left join question_practice_detail b on a.device_id = b.device_id where b.device_id is null and a.university = '复旦大学'