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

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

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

这道题实际可以拆解为两道类似的题目，只要理解了其中的一道，问题就解决了。其中一道是：请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数；

另一道是：请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年题目总练习次数。将上述两道题目分别查询出来后再进行join连接即可。

此题存在如下一些考察点：

多表连接、子查询、分组聚合，还包括一些常见函数的考察，包括ifnull函数、year()年份提取函数等，下面结合代码进行注解：

select
  s1.uid,
  exam_cnt,
  ifnull(question_cnt, 0)  //  ④ 查询指标 ： 由于题目练习量可能为空，因此采用ifnull函数取练习量为空时为0
from
  (
    select
      t1.uid uid,
      count(distinct t2.exam_id) exam_cnt
    from   //  ① 表连接
      exam_record t1
      left join examination_info t2 on t1.exam_id = t2.exam_id
      left join user_info t3 on t1.uid = t3.uid
    where
      t1.uid in (     //   ② 子查询：查询高难度SQL试卷得分平均值大于80并且是7级的红名大佬         select
          t1.uid     
        from
          exam_record t1
          left join examination_info t2 on t1.exam_id = t2.exam_id
          left join user_info t3 on t1.uid = t3.uid
        where
          level = 7
          and difficulty = "hard"
          and tag = "SQL"
        group by
          t1.uid
        having
          avg(score) > 80
      )  
      and year(submit_time) = "2021"   //  ② 仅查询2021年的作答信息
    group by   //  ③ 分组查询
      t1.uid
  ) s1
************* 上述部分为第一道题目，下述为第二道题目 ******************
    left join (
    select
      uid,
      count(question_id) question_cnt
    from
      practice_record
    where
      uid in (    //   ① 子查询：查询高难度SQL试卷得分平均值大于80并且是7级的红名大佬
        select
          a1.uid
        from
          exam_record a1
          left join examination_info a2 on a1.exam_id = a2.exam_id
          left join user_info a3 on a1.uid = a3.uid
        where
          level = 7
          and difficulty = "hard"
          and tag = "SQL"
        group by
          a1.uid
        having
          avg(score) > 80
      )
      and year(submit_time) = "2021"   //  ② 仅查询2021年的信息
    group by   //  ③ 分组查询
      uid
  ) s2 on s1.uid = s2.uid
order by
  exam_cnt asc,    //  ⑤ 按作答问卷数量升序，按题目练习量降序
  question_cnt desc