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

学会在提取的时候使用sum(if(某字段满足某条件，1,0), 排名后的rank系统默认为unsigned（无符号整数），不可以直接加减， 需要cast(rank as signed)之后才可以作为整数进行加减
分三步：
1.两表联立，作答表为主表，试卷信息表作为附表（题目说2021年上半年的试卷，这里不做特别提取，就是试卷信息表的全部试卷）
用sum（if(),1,0）写出2020上半年答题数，2021年上半年答题数
2.计算增长率，这里注意round（）保留位数，cast（，'%')写出保留一位数的百分比；rank over 计算排名，不用分区，按照2020上半年答题数、2021年上半年答题数分别降序排列就行
3.最后一层计算两个排名相减

with a as (
 select tag ,
        sum(if(DATE_FORMAT(submit_time,'%Y-%m') BETWEEN '2020-01' and '2020-06',1,0))exam_cnt_20,
                sum(if(DATE_FORMAT(submit_time,'%Y-%m') BETWEEN '2021-01' and '2021-06',1,0)) exam_cnt_21
 from exam_record b 
 left join examination_info a 
 on b.exam_id=a.exam_id
 group by tag),

 b as (select *,concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') growth_rate,rank() over(order by exam_cnt_20 desc) exam_cnt_rank_20,
 rank() over(order by exam_cnt_21 desc) exam_cnt_rank_21
 from a )

 select *, cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed) rank_delta
 from b 
 where exam_cnt_20!=0 and exam_cnt_21!=0
 order by 4 desc , 6 desc