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

select 
a.uid,
count(distinct a.id) exam_cnt,
count(distinct b.id) question_cnt
-- select *
from 
exam_record a left join 
practice_record b
on a.uid=b.uid
where a.uid in (
    select 
    a.uid
    from 
    exam_record a,
    user_info b,
    examination_info c
    where a.uid=b.uid
    and a.exam_id=c.exam_id
    and c.tag='SQL'
    and c.difficulty='hard'
    and b.level='7'
    group by a.uid
    having avg(a.score)>80
)
and (case when b.submit_time is not null then date_format(b.submit_time,'%Y')='2021' end or b.submit_time is null)
and date_format(a.submit_time,'%Y')='2021'
group by uid
order by exam_cnt asc,question_cnt desc