# 把21年和22年的先都放成示例的樣子
# 臨時表0，把各類試卷的20年和21年上半年的完成數(shù)提取出來
WITH temp_0 AS(
SELECT tag, 
    SUM(CASE WHEN DATE_FORMAT(submit_time, "%Y%m") BETWEEN 202001 AND 202006 THEN 1 ELSE 0 END) exam_cnt_20,
    SUM(CASE WHEN DATE_FORMAT(submit_time, "%Y%m") BETWEEN 202101 AND 202106 THEN 1 ELSE 0 END) exam_cnt_21
FROM examination_info a
JOIN exam_record b USING(exam_id)
GROUP BY tag
),
# 在0的基礎(chǔ)上，分別對20年和21年的完成數(shù)排序，直接rank
temp_1 AS(
SELECT tag, exam_cnt_20, exam_cnt_21, 
        RANK() OVER(ORDER BY exam_cnt_20 DESC) exam_cnt_20_rank,
        RANK() OVER(ORDER BY exam_cnt_21 DESC) exam_cnt_21_rank
FROM temp_0
)

# 主查詢，分別使用數(shù)量和排序進行兩個計算字段
# 百分比時要注意，mysql我嘗試了format，cast都不行，最終采用該方式
# 還有排名相減，要改成有符號的，不然報錯"BIGINT UNSIGNED value is out of range in '(`temp_1`.`exam_cnt_21_rank` - `temp_1`.`exam_cnt_20_rank`)'"
# 再排除掉某一年沒有完成數(shù)的試卷
#最后排序
SELECT tag, exam_cnt_20, exam_cnt_21, 
    CONCAT(ROUND((exam_cnt_21 - exam_cnt_20) / exam_cnt_20 * 100, 1), '%') growth_rate, 
    exam_cnt_20_rank, exam_cnt_21_rank, 
    (CAST(exam_cnt_21_rank AS SIGNED) - CAST(exam_cnt_20_rank AS SIGNED)) rank_delta
FROM temp_1
WHERE exam_cnt_20 > 0 AND exam_cnt_21 > 0
ORDER BY growth_rate DESC, exam_cnt_21_rank DESC

踩的坑：

1.沒看清題，以為是兩年，原來是兩個半年

2.百分數(shù)的構(gòu)成

3.排名數(shù)字相減，在MySQL中，BIGINT UNSIGNED 類型的值必須是非負的，范圍從 0 到 2^64-1。

問題原因是在進行減法操作 exam_cnt_21_rank - exam_cnt_20_rank 時，如果 exam_cnt_20_rank 大于 exam_cnt_21_rank，結(jié)果將是負數(shù)，這超出了 BIGINT UNSIGNED 類型的允許范圍。

解決方案：

1. 類型轉(zhuǎn)換:在進行減法運算之前，您可以將列轉(zhuǎn)換為有符號類型，比如 SIGNED。這樣即使結(jié)果是負數(shù)，也不會超出范圍。
2. 避免無符號整數(shù)類型:如果可能，考慮使用有符號的整數(shù)類型（如 BIGINT 而不是 BIGINT UNSIGNED），特別是在您預(yù)計結(jié)果可能為負的計算中。
3. 邏輯檢查:在執(zhí)行減法之前，您可以通過邏輯檢查來避免負結(jié)果，特別是當處理無符號類型時。