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

with exam_20 as (
    select tag,exam_cnt_20,rank() over (order by exam_cnt_20 desc) as exam_cnt_rank_20 
    from (
    select ei.tag,count(er.score) as exam_cnt_20
    from exam_record er inner join examination_info ei on er.exam_id=ei.exam_id
    where year(er.submit_time)=2020 and month(er.submit_time)<7
    group by ei.tag)t1
),
exam_21 as (
    select tag,exam_cnt_21,rank() over (order by exam_cnt_21 desc) as exam_cnt_rank_21 
    from (
    select ei.tag,count(er.score) as exam_cnt_21
    from exam_record er inner join examination_info ei on er.exam_id=ei.exam_id
    where year(er.submit_time)=2021 and month(er.submit_time)<7
    group by ei.tag)t2
)

select e21.tag,exam_cnt_20,exam_cnt_21,concat(round((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') as growth_rate,exam_cnt_rank_20,exam_cnt_rank_21,cast(exam_cnt_rank_21 as signed)-cast(exam_cnt_rank_20 as signed ) as rank_delta
from exam_21 e21 inner join exam_20 e20 on e21.tag=e20.tag
order by growth_rate desc,exam_cnt_rank_21 desc

最后居然卡在了cast的部分，这到底是什么道理

参考：https://www.cnblogs.com/xuwinwin/p/15942764.html

如果报错 BIGINT UNSIGNED value is out of range in...

SQL中：两数相减来做查询条件，由于两个字段都是unsigned的，并两个的大小是不一样。所以直接相减查询的时候，会出现负数，导致报错（Mysql不报错）

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in..

解决方法为：cast(num1 as signed)-cast(num2 as signed)