题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
比较容易理解的思路是：
1.先分别统计出每个学生8月答题的总数，和8月答对题的总数
2.再将user_profie这张表左外连接上述两张表
3.最后筛选复旦大学学生的数据即可
4.通过ifnull函数实现没答题，答题数目为0的需求。
select u.device_id, university, ifnull(ud.question_cnt, 0) as "question_cnt", ifnull(ud1.right_question_cnt, 0) as "right_question_cnt" from user_profile u left join ( select device_id, count(question_id) as "question_cnt" from question_practice_detail where date like "2021-08%" group by device_id ) as ud on u.device_id = ud.device_id left join ( select device_id, count(result) as "right_question_cnt" from question_practice_detail where date like "2021-08%" and result = 'right' group by device_id ) as ud1 on u.device_id = ud1.device_id where university = '复旦大学'
查看11道真题和解析