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

SELECT
    device_id,
    university,
    COUNT(question_id) AS question_cnt,
    SUM(right_question) AS right_question_cnt
FROM (
    SELECT
        up.device_id,
        university,
        IF(month(date) = 8, question_id, NULL) AS question_id,
        IF(result='right', 1, 0) AS right_question
    FROM user_profile up
    LEFT JOIN question_practice_detail qpd 
        ON up.device_id = qpd.device_id
    WHERE university = '复旦大学'
)t1
GROUP BY device_id, university

在子查询中处理好question_id这一属性，通过左连接可以使没有答题的同学的记录为None，再使用IF函数使“答题时间不在8月”的记录为None，而COUNT函数在累加时会将None的记录自动处理为0，故在主查询中使用COUNT函数聚合即可。此外对于“答题正确的数目”使用IF函数配合SUM进行聚合即可。