题解 | #统计复旦用户8月练题情况#

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、简单统计