题解 | #实习广场投递简历分析(三)#

WITH T AS
(
    SELECT
        job
        ,SUBSTR(date,1,7) MON
        ,SUM(num) CNT
    FROM resume_info
    WHERE SUBSTR(date,1,4) IN ('2025','2026')
    GROUP BY job,SUBSTR(date,1,7)
    ORDER BY MON DESC,CNT DESC
)
SELECT 
    T.job
    ,MAX(CASE WHEN SUBSTR(MON,1,4) = '2025' THEN MON END) first_year_mon
    ,MAX(CASE WHEN SUBSTR(MON,1,4) = '2025' THEN CNT END) first_year_cnt
    ,MAX(CASE WHEN SUBSTR(MON,1,4) = '2026' THEN MON END) second_year_mon
    ,MAX(CASE WHEN SUBSTR(MON,1,4) = '2026' THEN CNT END) second_year_cnt
FROM T
GROUP BY T.job,SUBSTR(MON,6,7)
ORDER BY first_year_mon DESC ,T.job DESC
;