题解 | #满足条件的用户的试卷完成数和题目练习数#

满足条件的用户的试卷完成数和题目练习数

https://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf

with cte_get_uid as (
    select uid
    from user_info
    join exam_record using(uid)
    join examination_info using(exam_id)
    where tag = 'SQL' and difficulty = 'hard' and `level` = 7
    group by uid
    having avg(score) >= 80
)
# 根据条件获取大佬uid
select uid,exam_cnt,
if(question_cnt is null,0,question_cnt)
# 特殊处理一下如果为空的情况
from (
    select uid,
    count(submit_time) as exam_cnt
    from exam_record
    join cte_get_uid using(uid)
    where submit_time is not null and year(submit_time) = 2021
    group by uid
) as t1
# 根据已知uid子查询中分组uid获得每人试卷完成数
left join (
    select uid,
    count(submit_time) as question_cnt
    from practice_record
    join cte_get_uid using(uid)
    where submit_time is not null and year(submit_time) = 2021
    group by uid
) as t2 using(uid)
# 根据已知uid子查询中分组uid获得每人题目完成数
order by exam_cnt asc,question_cnt desc
# 连接表之后根据做试卷和题目数量排序

全部评论

相关推荐

06-25 16:25
梧州学院 Java
愿汐_:项目介绍那么长,然而你做了啥就一句话?
点赞 评论 收藏
分享
06-23 11:28
门头沟学院 Java
牛客91966197...:也有可能是点拒绝的时候自动弹的话术
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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