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

创建临时表，增加三列——月份month、2025年的num、2026的num，然后再根据 job、月份month 进行group by 分组，通过 月份month 拼接成 2025-month、2026-month，得到 first_year_mon 和 second_year_mon，例如2025-01 和 2026-01 等。

select
    job,
    concat('2025-',month) as first_year_mon,
    sum(year2025) as first_year_cnt,
    concat('2026-',month) as second_year_mon,
    sum(year2026) as second_year_cnt
from
    (
        select
            *,
            date_format (date, '%m') as month,
            if (year (date) = 2025, num, 0) as year2025,
            if (year (date) = 2026, num, 0) as year2026
        from
            resume_info
    ) as temp
group by job, month
order by first_year_mon desc,job desc