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

这道题是我目前卡的最久的，主要是练习中总有一个none值，所以引用了select重新查了一次数据，所以代码有点冗余

根据题目要求 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户

我的第一反应是将2021年试卷总完成次数和题目总练习次数 full连接，所以查询条件应该是类似的

即Table1（select uid 和 cnt from 拼接表 然后where 筛选语句）

full join

Table2 （select uid 和 cnt from 拼接表 然后where 筛选语句）

结果发现table2中和table1中的uid不完全对应，所以加了一个select if(count is null,0,count)  感谢榜一大佬救我狗命

select 
    uid,
    exam_cnt,
    if(question_cnt is null, 0, question_cnt)
from
(select * from (select uid,
    count(submit_time) as exam_cnt
from exam_record
    left join user_info using(uid)
    left join examination_info using(exam_id)
where uid in
    (select uid from exam_record
        left join user_info using(uid)
        left join examination_info using(exam_id)
    where tag='SQL' and difficulty='hard' and level=7
    group by uid
    having avg(score)>80) and year(submit_time)=2021
group by uid) as t1

left join

(select uid,
    count(submit_time) as question_cnt
from practice_record
    left join user_info using(uid)
where uid in
    (select uid from exam_record
        left join user_info using(uid)
        left join examination_info using(exam_id)
    where tag='SQL' and difficulty='hard' and level=7
    group by uid
    having avg(score)>80) and year(submit_time)=2021
group by uid) as t2 using(uid)
order by exam_cnt asc, question_cnt desc) as tt;