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

WITH t1 AS (
    -- 第一步，先求出每个工作每个月的投递数量
	SELECT
    	job,
    	SUBSTR(date,1,7) AS first_year_mon,
    	SUM(num) AS first_year_cnt
    FROM resume_info
    WHERE SUBSTR(date,1,4) IN ('2025','2026')
    GROUP BY job, SUBSTR(date,1,7)
),
t2 AS (
    -- 第二步，找出2025年每个月的投递数量注意排序
	SELECT
    	job,
    	first_year_mon,
    	first_year_cnt
    FROM t1
    WHERE SUBSTR(first_year_mon,1,4) = '2025'
    ORDER BY first_year_mon DESC, job DESC
),
t3 AS (
	-- 第三步，找出2026年每个月的投递数量注意排序
    SELECT
    	job,
    	first_year_mon AS second_year_mon,
    	first_year_cnt AS second_year_cnt
    FROM t1
    WHERE SUBSTR(first_year_mon,1,4) = '2026'
    AND (job,SUBSTR(first_year_mon,6,2)) IN
    (SELECT job, SUBSTR(first_year_mon,6,2) FROM t2)
    ORDER BY second_year_mon DESC, job DESC
),
t4 AS (
	SELECT
    	t2.job,
    	t2.first_year_mon,
    	t2.first_year_cnt,
    	t3.second_year_mon,
    	t3.second_year_cnt
    FROM t2
    LEFT JOIN t3
    ON t2.job = t3.job
    AND SUBSTR(t2.first_year_mon,6,2) = SUBSTR(t3.second_year_mon,6,2)
    ORDER BY first_year_mon DESC, job DESC
)
SELECT * FROM t4;

# t3 里面的条件判断多个字符串最好用 IN 进行等值判断!