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

-- 参考了一个大佬的解题思路，如果只是平时的业务需求，可以取两次数，之后vlookup就可以了。但是这个的要求是同时取出来。
-- 唯一的难点在于表连接，这里只要是job、first_year、month三者相同，才能够保证是第二年的数据
select
    t1.job,
    t1.first_year_mon,
    t1.first_year_cnt,
    t2.second_year_mon,
    t2.second_year_cnt
from
    (
        select
            job,
            date_format (date, '%Y-%m') as first_year_mon,
            sum(num) as first_year_cnt
        from
            resume_info
        where
            year (date) = 2025
        group by
            job,
            first_year_mon
    ) as t1
    join (
        select
            job,
            date_format (date, '%Y-%m') as second_year_mon,
            sum(num) as second_year_cnt
        from
            resume_info
        where
            year (date) = 2026
        group by
            job,
            second_year_mon
    ) as t2 on t1.job = t2.job
AND left(t1.first_year_mon,4) = left(t2.second_year_mon,4) - 1
AND substr(t1.first_year_mon,6,2) = substr(t2.second_year_mon,6,2)
order by first_year_mon desc,job desc;