SQL141 試卷完成數同比2020年的增長率及排名變化

withtemp as (selectexam_id,tag,date(submit_time) as submit_timefromexamination_infoleft join exam_record using (exam_id)wheresubmit_time is not null),2021_temp as (selecttag,count(*) as exam_cnt_21,rank() over (order bycount(*) desc) as exam_cnt_rank_21fromtempwhereyear(submit_time) = 2021and (month(submit_time) between 1 and 6)group bytag),2020_temp as (selecttag,count(*) as exam_cnt_20,rank() over (order bycount(*) desc) as exam_cnt_rank_20fromtempwhereyear(submit_time) = 2020and (month(submit_time) between 1 and 6)group bytag)
selecttag,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,-- 修改為使用SIGNED轉換，避免無符號整數減法問題CAST(exam_cnt_rank_21 AS SIGNED) - CAST(exam_cnt_rank_20 AS SIGNED) AS rank_delta
from2020_tempjoin 2021_temp using (tag)
order bygrowth_rate desc,exam_cnt_rank_21 desc

說明

因為在計算rank_delta時，2021年的排名可能小于2020年的排名，導致無符號整數減法結果為負數，超出了BIGINT UNSIGNED的范圍。

在MySQL中，當使用無符號整數(UNSIGNED)進行減法運算時，如果結果為負數，會報錯"BIGINT UNSIGNED value is out of range"。