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

SELECT 
u.device_id,
u.university,
SUM(IF(q.result IS NOT NULL,1,0)) As question_cnt,
SUM(IF(q.result='right',1,0)) AS right_question_cnt
FROM(
SELECT device_id,university
    FROM
    user_profile
    WHERE
    university='复旦大学'
) AS u
LEFT JOIN(
SELECT  device_id,result
    FROM
    question_practice_detail
    WHERE
    MONTH(date)=8
) AS q
ON 
q.device_id=u.device_id
GROUP BY
q.device_id