题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
SELECT up.device_id, up.university, COUNT(qd.question_id) AS question_cnt, SUM(IF(qd.result = 'right', 1, 0)) AS right_question_cnt FROM user_profile AS up LEFT OUTER JOIN question_practice_detail AS qd ON up.device_id = qd.device_id AND MONTH(qd.date) = 8 WHERE up.university = '复旦大学' GROUP BY up.device_id, up.university;
1.区分过滤条件和联结条件。若将条件放在ON后,则表明必须满足所有条件时进行联结,而WHERE条件表示在分组前进行过滤,若将AND MONTH(qd.date) = 8放在WHERE条件里,将会过滤掉没有答题记录的人。
2.GROUP BY 后跟聚合键,聚合键要出现在SELECT语句里;出现在SELECT语句里的字段也要出现在GROUP BY里,再跟聚合函数。