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

# 外连接
select up.device_id, up.university, 
COALESCE(COUNT(qpd.question_id), 0) AS question_cnt,
COALESCE(SUM(CASE WHEN qpd.result = 'right' THEN 1 ELSE 0 END), 0) AS right_question_cnt
# COALESCE(COUNT(IF(qpd.result = 'right',1,NULL)),0) AS right_question_cnt  写法2
FROM user_profile AS up
LEFT JOIN question_practice_detail AS qpd 
ON up.device_id = qpd.device_id AND month(qpd.date) = 8  #8月的
WHERE up.university = '复旦大学' #复旦大学
GROUP BY device_id  #id进行分组