思路

这是一个看上去很难的题目：

高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷完成数和题目练习数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

要求一堆，所以我们来分解一下：

1.高难度SQL试卷得分平均值大于80并且是7级

2.2021年试卷完成数和题目练习数

3.试卷完成数升序，按题目练习数降序

其实一共就这么多

1是uid in 所以我们可以放后边考虑，3一个orderby 完事，首先来看看**2 **

2在题目上还要求只保留2021年有试卷完成记录的用户但是计数2021年试卷完成数和题目练习数不就把该筛选的筛出去了？所以我们直接

2.1试卷完成数

select
    e_r.uid,
    sum(if(year(e_r.submit_time)=2021,1,0)) exam_cnt
from exam_record e_r
group by e_r.uid

是2021年的置1，否则置零让然后求和，如法炮制

2.2题目练习数

select
   p_r.uid,
   sum(if(year(p_r.submit_time)=2021,1,0)) question_cnt
from practice_record p_r
group by p_r.uid

或者

2. 2021年试卷完成数和题目练习数

select e_r.uid  uid,
       count(distinct e_r.exam_id)  exam_cnt,
       count(distinct p_r.id)  question_cnt
from 
     exam_record e_r
left join 
    practice_record p_r
on 
    e_r.uid=p_r.uid 
    and year(e_r.submit_time)=2021 
    and year(p_r.submit_time)=2021

直接筛出来2021 然后去重数人头。

1.1 7级大佬

select
   uid
from
   user_info u_i
WHERE
       level = 7

1.2 8分 SQL hard

SELECT
 e_r.uid
FROM
 exam_record e_r
     LEFT JOIN
 examination_info e_i
 on
  e_r.exam_id = e_i.exam_id
where
     difficulty = 'hard'and
     tag = 'SQL'
group by
 e_r.uid
HAVING
     avg(score)>80

1.1+1.2高难度SQL试卷得分平均值大于80并且是7级

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

1+2+3 就是通过代码

select
       exam.uid,
       exam_cnt,
       ifnull(question_cnt,0) question_cnt
from (select
          e_r.uid,
          sum(if(year(e_r.submit_time)=2021,1,0)) exam_cnt
      from exam_record e_r
      group by e_r.uid) exam
left join (select
               p_r.uid,
               sum(if(year(p_r.submit_time)=2021,1,0)) question_cnt
           from practice_record p_r
           group by p_r.uid) que
on exam.uid = que.uid
where exam.uid in (
    select
        seven.uid
    from (
             #7级大佬
             select
                 uid
             from
                 user_info u_i
             WHERE
                     level = 7
         ) seven
             join
         (
#高难度 tag = ‘sql‘ avg>80
             SELECT
                 e_r.uid
             FROM
                 exam_record e_r
                     LEFT JOIN
                 examination_info e_i
                 on
                         e_r.exam_id = e_i.exam_id
             where
                     difficulty = 'hard'and
                     tag = 'SQL'
             group by
                 e_r.uid
             HAVING
                     avg(score)>80
         ) tag
         on
                 tag.uid = seven.uid
    )
order by
    exam_cnt,question_cnt desc ;

或者

select e_r.uid  uid,
       count(distinct e_r.exam_id)  exam_cnt,
       count(distinct p_r.id)  question_cnt
from 
     exam_record e_r
left join 
    practice_record p_r
on 
    e_r.uid=p_r.uid 
    and year(e_r.submit_time)=2021 
    and year(p_r.submit_time)=2021
where e_r.uid in(
    select 
           e_r.uid
    from 
         exam_record e_r
    left join examination_info e_i
    on 
        e_r.exam_id = e_i.exam_id
    left join user_info ui
    on 
        e_r.uid = ui.uid
    where 
          tag='SQL' and difficulty='hard'
          and level = 7
    group by 
        e_r.uid
    having avg(score) > 80
)
group by 
    e_r.uid
order by 
    exam_cnt,question_cnt desc