题解 | #试卷完成数同比2020年的增长率及排名变化#

select
t.tag,
sum(case when t.years=2020 then t.m end),
sum(case when t.years=2021 then t.m end),
concat(
round(
((sum(case when t.years=2021 then t.m end)/sum(case when t.years=2020 then t.m end))-1)*100,1),"%") as zzl,
sum(case when t.years=2020 then t.d end),
sum(case when t.years=2021 then t.d end) as pm,
sum(case when t.years=2021 then t.d end)-
sum(case when t.years=2020 then t.d end)
from(
select
ef.tag,
year(ed.submit_time) as years,
sum(case when ed.submit_time is not null then 1 else 0 end) as m,
rank()over(partition by year(ed.submit_time) order by sum(case when ed.submit_time is not null then 1 else 0 end) desc) as d,
count(*)over(partition by ef.tag) as py
from exam_record ed left join examination_info ef 
on ed.exam_id=ef.exam_id
where
month(ed.submit_time)<=6
and
ed.submit_time is not null
group by ef.tag,years
) as t 
where
t.py>=2
group by t.tag
order by zzl desc,pm desc