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

题目逻辑不难，但需要建立新表再连接，这种类型的题目建议先用with t as 建立新表再计算需要的指标，这样思路更清晰。

1：分别建立2020和2021上半年各类型的试卷的完成数以及排名表t1和t2，排名使用rank()over(partition by )即可。

2：连接t1和t2，求出所需指标即可

特别注意点 排名差值相减时出现负数会报错，因为rank函数没有符号。 因此考虑使用cast()函数将排名转化为数字再相减

with t1 as 
(select tag,count(submit_time) as com,
rank()over(order by count(submit_time) desc ) as rk
from exam_record as b join examination_info as a on a.exam_id=b.exam_id
where submit_time between '2020-01-01' and '2020-06-30'
group by tag ),
t2 as 
(select tag,count(submit_time) as co,
rank()over(order by count(submit_time) desc ) as rn
from exam_record as b join examination_info as a on a.exam_id=b.exam_id
where submit_time between '2021-01-01' and '2021-06-30'
group by tag )

select t1.tag,t1.com,t2.co,concat(round((co-com)/com*100,1),'%') as gr,
rk,rn,cast(rn as signed)-cast(rk as signed)
from t1 join t2 on t1.tag=t2.tag
order by gr desc,rk desc