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

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

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

select
    t1.uid,
    count(distinct t2.id) as exam_cnt,
    count(distinct t3.id) as question_cnt
from
    (
        select
            ui.uid
        from
            exam_record er
            join user_info ui using (uid)
            join examination_info ei using (exam_id)
        where
            ei.tag = 'SQL'
            and ei.difficulty = 'hard'
            and year (er.submit_time) = '2021'
            and ui.level = 7
        group by
            ui.uid
        having
            avg(er.score) > 80
    ) t1
    left join exam_record t2 on t1.uid = t2.uid
    and year (t2.submit_time) = '2021'
    left join practice_record t3 on t1.uid = t3.uid
    and year (t3.submit_time) = '2021'
group by
    uid
order by
    exam_cnt asc,
    question_cnt desc

1.先通过条件查出 高难度SQL试卷得分平均值大于80并且是7级的红名大佬,并且 2021年有试卷完成记录的用户

2.连表查询 统计他们的2021年试卷总完成次数和题目总练习次数

全部评论

相关推荐

frutiger:逆天,我家就安阳的,这hr咋能说3k的,你送外卖不比这工资高得多?还说大厂来的6k,打发叫花子的呢?这hr是怎么做到说昧良心的话的
找工作时遇到的神仙HR
点赞 评论 收藏
分享
点赞 评论 收藏
分享
评论
4
1
分享

创作者周榜

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