题解 | #统计每个学校的答过题的用户的平均答题数#

统计每个学校的答过题的用户的平均答题数

https://www.nowcoder.com/practice/88aa923a9a674253b861a8fa56bac8e5

#方法1 cross join where
# select university,round(count(qpd.device_id)/count(distinct(qpd.device_id)),4) as avg_answer_cnt
# from user_profile as up
# cross join question_practice_detail as qpd
# where up.device_id=qpd.device_id
# group by up.university
# order by up.university asc

#方法2 inner join on
# select up.university,round(count(qpd.device_id)/count(distinct(qpd.device_id)),4) as avg_answer_cnt
# from user_profile as up
# inner join question_practice_detail as qpd
# on up.device_id=qpd.device_id
# group by up.university
# order by up.university asc

#方法3 right join,因为up表中可能有没有答题的,所以left join会有up表中多余id的空值,qpd表中肯定是答过题的,因此以qpd表为主用right join
select up.university,round(count(qpd.device_id)/count(distinct(qpd.device_id)),4) as avg_answer_cnt
from  user_profile as up
right join  question_practice_detail as qpd
on up.device_id=qpd.device_id
group by up.university
order by up.university asc

全部评论

相关推荐

牛客37185681...:马德,我感觉这是我面过最恶心的公司,一面是两个女hr,说什么实习前几个月属于试用期,试用期过了才能转成正式实习生,我***笑了,问待遇就是不说,问能不能接受全栈,沙币公司
如果可以选,你最想去哪家...
点赞 评论 收藏
分享
09-19 13:59
门头沟学院 Java
用微笑面对困难:Trae一下,如果真成了,他用了直接发字节起诉代码版权,,这个代码不商用是没问题的如果没成也是情理之中的。
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务