题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
结果
select up.device_id, university, ifnull(qpd.question_cnt,0) as question_cnt, ifnull(qpd.right_question_cnt,0) as right_question_cnt from user_profile up left join ( select device_id, count(question_id) as question_cnt, sum(if(result='right',1,0)) as right_question_cnt from question_practice_detail where date_format(date,'%Y-%m')='2021-08' group by device_id) as qpd on up.device_id = qpd.device_id where university="复旦大学"
过程
一步一步来 从题目中提取关键字:复旦大学、每个用户、八月份、总题目数、正确题目数 ,分解步骤
(1)先从question_practice_detail表中 找出八月份 每个用户的答的总题目数和正确题目数 作为表qpd
select device_id, count(question_id) as question_cnt, sum(if(result='right',1,0)) as right_question_cnt from question_practice_detail where date_format(date,'%Y-%m')='2021-08' group by device_id
(2)将user_profile表与qpd进行左连接,选出学校等于复旦大学的用户记录
select up.device_id, university, ifnull(qpd.question_cnt,0) as question_cnt, ifnull(qpd.right_question_cnt,0) as right_question_cnt from user_profile up left join ( select device_id, count(question_id) as question_cnt, sum(if(result='right',1,0)) as right_question_cnt from question_practice_detail where date_format(date,'%Y-%m')='2021-08' group by device_id) as qpd on up.device_id = qpd.device_id where university="复旦大学"