题解 | 统计复旦用户8月练题情况
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
SELECT
up.device_id,
up.university,
COUNT(qpd.question_id) AS question_cnt,
COUNT(CASE WHEN qpd.result='right' THEN qpd.question_id 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 up.university = '复旦大学'
GROUP BY up.device_id, up.university;
用qpd表里匹上的date为null来实现没答题算0
