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

select up.device_id, up.university, coalesce(new.question_cnt, 0) AS question_cnt,
    coalesce(new.right_question_cnt, 0) AS right_question_cnt from user_profile as up 
left join 
(select device_id, count(question_id) as question_cnt , sum(if(result = 'right',1,0)) as right_question_cnt from question_practice_detail where month(date)=8 group by device_id) as new 
on up.device_id=new.device_id where university='复旦大学'

使用 COALESCE 函数是为了确保即使在子查询中没有找到匹配的记录（即没有练习记录），也能返回一个默认值（通常是 0）