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

• 找到大佬们id

  • 连接exam_record和examination_info，user_info
  • 条件：高难度SQL试卷得分平均值大于80并且是7级
  • 分组：uid

    select 
      uid 
    from user_info
    left join exam_record using(uid)
    left join examination_info using(exam_id)
    where difficulty = 'hard'
    and tag = 'SQL'
    and level = 7
    group by uid
    having avg(score) >80

• 分别统计用户的试卷完成数，题目完成数

  • 试卷数和题目数在两张表中，分别查询，再连到一起
    • 得到exam表和question表
  • 条件：2021
  • 按照uid分组

• 联立上面两部

  • 条件：当用户是大佬时
  • 排序

select 
    uid,
    exam_cnt,
    question_cnt
from(
    -- 试卷总完成次数
    select 
        uid,
        count(submit_time) as exam_cnt
    from exam_record
    where year(submit_time) =2021
    group by uid
) exam
join (
    -- 题目总完成次数
    select 
        uid,
        count(submit_time) as question_cnt
    from practice_record
    where year(submit_time) =2021
    group by uid
    ) question
using(uid)
where uid in (
    select 
        uid 
    from user_info
    left join exam_record using(uid)
    left join examination_info using(exam_id)
    where difficulty = 'hard'
    and tag = 'SQL'
    and level = 7
    group by uid
    having avg(score) >80
)
order by exam_cnt,question_cnt desc