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

with temp_table_1 as(
    select job,LEFT(date,7) AS first_year_mon ,sum(num) as first_year_cnt from resume_info
    where date< '2026-01-01'
    group by job,first_year_mon
),
temp_table_2 as(
    select job,LEFT(date,7) AS second_year_mon ,sum(num) as second_year_cnt from resume_info
    where date>= '2026-01-01' and date < '2027-01-01'
    group by job,second_year_mon
)
select te_1.job,te_1.first_year_mon,te_1.first_year_cnt,te_2.second_year_mon,te_2.second_year_cnt
from temp_table_1 te_1,temp_table_2 te_2 
where te_1.job=te_2.job and right(te_1.first_year_mon,2) = right(te_2.second_year_mon,2)
order by te_1.first_year_mon desc,te_1.job desc