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

with u_tb as (
    # 查询等级为7的uid
    select
    uid
    from user_info
    where level = '7'
)
, e_avg_tb as (
    # 查询SQL试卷的试卷平均分
    select
    uid,
    round(avg(score),0) s_avg
    from exam_record e 
    inner join (
        select * from examination_info where tag = 'SQL'
    ) ei on e.exam_id = ei.exam_id
    group by uid
)
, id_tb as (
    # 筛选出平均分大于80且进行内连接去掉匹配失败的
    select
    e.uid uid
    from e_avg_tb e
    inner join u_tb u on u.uid=e.uid
    where s_avg>80
)
,e_cnt as (
    # 提前算出2021年通过测试的每个用户的id
    select
    uid,
    count(uid) as exam_cnt
    from exam_record
    where uid in (select * from id_tb) and date_format(submit_time,'%Y')='2021'
    group by uid
)
,p_cnt as(
    # 找出2021年复合要求的练习记录并进行去重
    select
    uid,
    question_id
    from practice_record
    where uid in (select * from id_tb) and date_format(submit_time,'%Y')='2021'
    group by uid,question_id,submit_time
)
# 计算练习记录结果并进行左连接将数据汇总到一个表中，进行排序
select
id_tb.uid uid,
e_cnt.exam_cnt exam_cnt,
count(p_cnt.question_id) question_cnt
from id_tb 
left join e_cnt on id_tb.uid = e_cnt.uid
left join p_cnt on id_tb.uid = p_cnt.uid
group by id_tb.uid
order by exam_cnt,question_cnt desc