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

思路：分别统计符合要求uid的2021年试卷总完成次数和题目总练习次数，然后连表

t和t1筛选符合要求uid；

t2储存符合要求uid的2021年试卷总完成次数

t3储存符合要求uid的2021年题目总练习次数

连表t2 left join t3，连接过程question_cnt可能产生none（t2两条记录，t3只有一条），用if把none换成0。exam_cnt没必要

with t as (
select uid,avg(score) as avg_sc
from exam_record t left join examination_info t1 on t.exam_id=t1.exam_id
where tag='SQL' and difficulty = 'hard'
group by uid),
t1 as (
select t.uid as uid
from t left join user_info t1 on t.uid=t1.uid
where level = 7 and avg_sc > 80),
t2 as(
select uid,count(score) as exam_cnt
from exam_record
where uid in (select uid from t1) and year(start_time) = 2021
group by uid),
t3 as (
select uid,count(score) as question_cnt
from practice_record
where uid in (select uid from t1) and year(submit_time) = 2021
group by uid)

select t2.uid as uid,
if(exam_cnt is null,0,exam_cnt) as exam_cnt,
if(question_cnt is null,0,question_cnt) as question_cnt
from t2 left join t3 on t2.uid=t3.uid
where exam_cnt<>0
order by exam_cnt,question_cnt desc