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

比较简单的思路，给每一排创建一个关于”2020年上半年“或“2021年上半年”的标签，然后筛选就可以了。 后面看了大佬的思路发现我用case when 打标签还不如直接用case when 把日期给算出来，画蛇添足了。

WITH a1 AS (
           SELECT a.tag,
                  er.*,
                  CASE WHEN DATE_FORMAT(start_time,'%Y%m') BETWEEN '202001' AND '202006' THEN '2020年上半年'
                       WHEN DATE_FORMAT(start_time,'%Y%m') BETWEEN '202101' AND '202106' THEN '2021年上半年' END AS "年标签"
           FROM examination_info a JOIN exam_record er ON a.exam_id = er.exam_id
           ),
a2 AS(
    SELECT tag,SUM(CASE 年标签 WHEN '2020年上半年' THEN 1 ELSE 0 END) AS exam_cnt_20,
           SUM(CASE 年标签 WHEN '2021年上半年' THEN 1 ELSE 0 END) AS exam_cnt_21     
    FROM a1
    WHERE submit_time IS NOT NULL
    AND 年标签 IN ('2020年上半年','2021年上半年')
    GROUP BY tag
    ),
a3 AS(
    SELECT tag,exam_cnt_20,exam_cnt_21,
           CONCAT(ROUND((exam_cnt_21-exam_cnt_20)/exam_cnt_20*100,1),'%') AS growth_rate,
           RANK()OVER(ORDER BY exam_cnt_20 DESC) AS exam_cnt_rank_20,
           RANK()OVER(ORDER BY exam_cnt_21 DESC) AS exam_cnt_rank_21,
           CAST(RANK()OVER(ORDER BY exam_cnt_21 DESC) AS SIGNED) - CAST(RANK()OVER(ORDER BY exam_cnt_20 DESC) AS SIGNED) AS rank_delta
    FROM a2
    )
SELECT *
FROM a3
WHERE tag IN (SELECT tag FROM a2 WHERE exam_cnt_20 !=0 AND exam_cnt_21 != 0)
ORDER BY growth_rate DESC,exam_cnt_rank_21 DESC;