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

# #高难度SQL试卷得分平均值大于80并且是7级的红名大佬
# #统计2021年的试卷完成次数
# #count(*) as exam_cnt
# select uid,count(*) as exam_cnt
# from exam_record as er 
# join examination_info as ei on er.exam_id=ei.exam_id
# join user_info as ui on er.uid=ui.uid
# where ei.difficulty="hard"
# and ei.tag="SQL"
# group by er.uid
# having avg(er.score)>80

# left join

# #题目总练习次数
# (select count(distinct uid) as question_cnt
# from practice_record as pr
# join user_info as ui on pr.uid=ui.uid
# group by pr.uid )

# order by exam_cnt,question_cnt DESC;

select 
    uid,
    exam_cnt,
    if(question_cnt is null, 0, question_cnt)
from
(select 
    uid,
    count(submit_time) as exam_cnt
from exam_record
where YEAR(submit_time) = 2021
group by uid) t

left join

(select
    uid,
    count(submit_time) as question_cnt
from practice_record
where YEAR(submit_time) = 2021
group by uid) t2 using(uid)

where uid in
(
select
    uid
from exam_record
join examination_info using(exam_id)
join user_info using(uid)
where tag = 'SQL' and difficulty = 'hard' and `level` = 7
group by uid
having avg(score) >= 80
)
order by exam_cnt asc, question_cnt desc