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

正确

SELECT u.device_id, university, 
    count(question_id) question_cnt,
    sum(IF(result="right", 1 ,0)) right_question_cnt
FROM user_profile AS u
left JOIN question_practice_detail AS qpd
ON u.device_id = qpd.device_id
and YEAR(date) = 2021
and MONTH(date) = 08
WHERE university = "复旦大学"
group by device_id;

错误1

SELECT u.device_id, university, 
    count(question_id) question_cnt,
    sum(IF(result="right", 1 ,0)) right_question_cnt
FROM user_profile AS u
left JOIN question_practice_detail AS qpd
ON u.device_id = qpd.device_id
WHERE university = "复旦大学"
and YEAR(date) = 2021
and MONTH(date) = 08
group by device_id;

错误2

SELECT u.device_id, university, 
    count(question_id) question_cnt,
    sum(IF(result="right", 1 ,0)) right_question_cnt
FROM user_profile AS u
left JOIN question_practice_detail AS qpd
ON u.device_id = qpd.device_id
and YEAR(date) = 2021
and MONTH(date) = 08
and university = "复旦大学"
group by device_id;