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

# 方法1
SELECT up.device_id,up.university,COUNT(qpd.device_id) AS question_cnt,
SUM(CASE WHEN qpd.result = 'right' THEN 1 ELSE 0 END) AS right_question_cnt  
FROM user_profile up 
LEFT JOIN question_practice_detail qpd ON up.device_id = qpd.device_id 
WHERE up.university = '复旦大学' AND (MONTH(DATE)='08' OR DATE IS NULL)
GROUP BY up.device_id,up.university

# 方法2
SELECT
    up.device_id,
    up.university,
    COUNT(qpd.device_id) AS question_cnt,
    SUM(
        CASE
            WHEN qpd.result = 'right' THEN 1
            ELSE 0
        END
    ) AS right_question_cnt
FROM
    user_profile up
    LEFT JOIN question_practice_detail qpd ON up.device_id = qpd.device_id
    AND MONTH (qpd.date) = 8
WHERE
    university = '复旦大学'
GROUP BY
    up.device_id