Mysql 解法, 执行用时: 37ms, 内存消耗: 6380KB, 提交时间: 2022-01-27

select er1.uid as uid,
       count(distinct er1.exam_id) as exam_cnt,
       count(distinct pr1.id) as question_cnt
from exam_record er1
left join practice_record pr1 on er1.uid = pr1.uid and year(er1.submit_time)=2021  and YEAR(pr1.submit_time)=2021
where er1.uid in (
    select er.uid
from user_info ui
left join exam_record er on ui.uid = er.uid
join examination_info ei on er.exam_id = ei.exam_id
where  ei.tag = 'SQL' and ui.level = 7 and difficulty = 'hard' and year(submit_time)=2021
group by ui.uid
having avg(er.score) >80)
group by er1.uid
order by exam_cnt , question_cnt desc;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6396KB, 提交时间: 2021-12-02

with info as(
select DISTINCT uid
    from examination_info inner join exam_record using(exam_id)
    inner  join user_info using (uid)
    where difficulty='hard'
        and tag='SQL'
        and submit_time is not NULL
        and level=7
        group by uid
        having avg(score)>80
)
select DISTINCT user_info.uid,
    count(distinct exam_record.exam_id)as exam_cnt,
    count(distinct practice_record.submit_time)as question_cnt
    from user_info left outer join exam_record 
    on(user_info.uid=exam_record.uid 
       and extract(year from exam_record.submit_time)=2021)
    left outer  join practice_record 
    on(user_info.uid=practice_record.uid
      and extract(year from practice_record.submit_time)=2021)
    where  user_info.uid in (select * from info)
    group by user_info.uid
    order by exam_cnt,user_info.uid desc

Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2022-01-27

SELECT
d.uid
,count(distinct case  when substr(d.submit_time,1,4)='2021' then d.id else null  end ) exam_cnt
,count(distinct case  when substr(e.submit_time,1,4)='2021' then e.id else null  end ) question_cnt 
from
exam_record d left join
practice_record e
on d.uid=e.uid
where d.uid in
(
select
a.uid
from
user_info  a join  exam_record b
on a.uid=b.uid
join examination_info c
on c.exam_id=b.exam_id
where c.tag='sql' and c.difficulty='hard'
and a.level>='7'and substr(submit_time,1,4)='2021'
group by  a.uid
having avg(b.score)>='80') and  substr(d.submit_time,1,4)='2021'
group by d.uid
order by exam_cnt,question_cnt desc;

Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2021-11-30

with target_user as
(#先是筛选特定的用户
select 
b.uid,
avg(b.score)
from examination_info a 
left join exam_record b on a.exam_id=b.exam_id
inner join user_info c on b.uid=c.uid and c.level=7
where a.tag='SQL' and a.difficulty='hard' 
group by b.uid
having avg(b.score)>=80
)

#接下来连接三个表，因为exam_record和practice_record 的主键是id,
#所以计算次数时可以用dsitinct id
select 
c.uid,
count(distinct a.id) as exam_cnt,
count(distinct b.id) as question_cnt
from target_user c
left join exam_record a on a.uid =c.uid and year(a.submit_time)=2021 
left join practice_record b on b.uid=c.uid and year(b.submit_time)=2021
group by c.uid
order by exam_cnt,question_cnt desc

Mysql 解法, 执行用时: 37ms, 内存消耗: 6404KB, 提交时间: 2022-01-22

select 
t1.uid,
-- count(distinct case when year(t2.submit_time) = '2021' then t2.exam_id else null end) as exam_cnt, -- 不对
-- count(distinct case when year(t3.submit_time) = '2021' then t3.question_id else null end) as question_cnt -- 不对
count(distinct case when year(t2.submit_time) = '2021' then t2.id else null end) as exam_cnt, -- 正确	
count(distinct case when year(t3.submit_time) = '2021' then t3.id else null end) as question_cnt -- 正确
from 
(
	select A.uid
	from exam_record AS A
    INNER JOIN user_info AS B
    ON A.uid = B.uid 
    INNER JOIN examination_info AS C
    ON A.exam_id = C.exam_id
    where B.level  = 7 
	      and 
          C.tag = 'SQL' 
          and 
          C.difficulty = 'hard'
	group by uid 
	having avg(score) > 80 
) t1 
 -- join -- 不能用join
left join 
exam_record t2 
on t1.uid = t2.uid 
 -- join -- 不能用join
left join 
practice_record t3 
on t1.uid = t3.uid 
-- where year(t2.submit_time) = '2021' and  year(t3.submit_time) = '2021' -- 不能在这里统一用where卡条件！！
group by t1.uid
order by exam_cnt asc , question_cnt desc ;
-- 结果按试卷完成数升序，按题目练习数降序