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

with cte_get_uid as (
    select uid
    from user_info
    join exam_record using(uid)
    join examination_info using(exam_id)
    where tag = 'SQL' and difficulty = 'hard' and `level` = 7
    group by uid
    having avg(score) >= 80
)
# 根据条件获取大佬uid
select uid,exam_cnt,
if(question_cnt is null,0,question_cnt)
# 特殊处理一下如果为空的情况
from (
    select uid,
    count(submit_time) as exam_cnt
    from exam_record
    join cte_get_uid using(uid)
    where submit_time is not null and year(submit_time) = 2021
    group by uid
) as t1
# 根据已知uid子查询中分组uid获得每人试卷完成数
left join (
    select uid,
    count(submit_time) as question_cnt
    from practice_record
    join cte_get_uid using(uid)
    where submit_time is not null and year(submit_time) = 2021
    group by uid
) as t2 using(uid)
# 根据已知uid子查询中分组uid获得每人题目完成数
order by exam_cnt asc,question_cnt desc
# 连接表之后根据做试卷和题目数量排序