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

WITH t1 AS(
    SELECT tag, start_year, exam_cnt_20, rank() over (ORDER BY exam_cnt_20 DESC) exam_cnt_rank_20 
    FROM (SELECT tag, YEAR(submit_time) start_year, COUNT(submit_time) exam_cnt_20
          FROM exam_record, examination_info
          WHERE exam_record.exam_id=examination_info.exam_id
          AND YEAR(submit_time)=2020 AND MONTH(submit_time)<7
          GROUP BY tag,start_year) tt1),
    t2 AS(
    SELECT tag, start_year, exam_cnt_21, rank() over (ORDER BY exam_cnt_21 DESC) exam_cnt_rank_21
    FROM (SELECT tag, YEAR(submit_time) start_year, COUNT(submit_time) exam_cnt_21
          FROM exam_record, examination_info
          WHERE exam_record.exam_id=examination_info.exam_id
          AND YEAR(submit_time)=2021 AND MONTH(submit_time)<7
          GROUP BY tag,start_year) tt2)
     

SELECT t1.tag, exam_cnt_20, exam_cnt_21, 
CONCAT(ROUND((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1), '%') growth_rate,
exam_cnt_rank_20, exam_cnt_rank_21, CAST(exam_cnt_rank_21 AS SIGNED)-CAST(exam_cnt_rank_20 AS SIGNED)
FROM t1,t2
WHERE t1.tag=t2.tag
ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC;

思路是先做出20年的信息表，21年的信息表，之后两表一计算即可。

mark的是CAST(xxx AS SIGNED)的用法。CAST函数语法规则是：Cast(字段名 as 转换的类型)， SIGNED表示转化为int型。

上面代码直接exam_cnt_rank_21和exam_cnt_rank_20相减会报错。mysql 两个字段相减，当其中一个或两个字段的类型的unsigned无签名类型，相减的值小于0时，会报错。