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

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