题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
select distinct B.device_id,B.university,COUNT(B.result) as question_cnt, sum(B.right) AS right_question_cnt from (select A.device_id,A.university,Q.question_id,Q.result, case when Q.result = 'right' then 1 else 0 end as 'right' from (select device_id,university from user_profile where university = '复旦大学') A left join question_practice_detail Q on A.device_id = Q.device_id and Q.date like '2021-08%') B group by device_id
1、筛选出复旦大学的学生device_id,university,作为表A
select device_id,university from user_profile where university = '复旦大学'
2、A表与question_practice_detail表左连接 连接条件
A.device_id = Q.device_id and Q.date like '2021-08%',
选出想要的列
A.device_id,A.university,Q.question_id,Q.result,case when Q.result = 'right' then 1 else 0 end as 'right'
作为表B
3、简单统计