题解 | #统计复旦用户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、简单统计

全部评论

相关推荐

不愿透露姓名的神秘牛友
10-05 10:13
已编辑
HHHHaos:让这些老登来现在秋招一下,简历都过不去
点赞 评论 收藏
分享
点赞 收藏 评论
分享
牛客网
牛客企业服务