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

#八月份做题个数:SUM( IF( MONTH(date) = 8, 1, 0) )
#八月份做对题个数:SUM( IF( MONTH(date) = 8 AND result = 'right', 1 ,0) )

SELECT UP.device_id, UP.university, 
        SUM( IF( MONTH(date) = 8, 1, 0) ) AS question_cnt,
        SUM( IF( MONTH(date) = 8 AND result = 'right', 1 ,0) ) AS right_question_cnt
FROM user_profile AS UP
LEFT JOIN question_practice_detail AS QPD
ON UP.device_id = QPD.device_id
WHERE university = '复旦大学'
GROUP BY UP.device_id;