1、取2025年符合条件的信息
select job ,DATE_FORMAT(date,'%Y-%m') as first_year_mon, sum(num) as first_year_cnt from resume_info where date between '2025-01-01' and '2025-12-31' group by job,first_year_mon)2、取2026年符合条件的信息
select job ,DATE_FORMAT(date,'%Y-%m') as second_year_mon, sum(num) as second_year_cnt from resume_info where date between '2026-01-01' and '2026-12-31' group by job,second_year_mon3、1表和2表连接,按job和相同月份匹配(注1表和2表中的日期经过date_format())函数已经转为了字符串,需使用截取字符串函数right()取对应的月份字符串)
select a.job,a.first_year_mon,a.first_year_cnt, b.second_year_mon,b.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 date between '2025-01-01' and '2025-12-31' group by job,first_year_mon) as a left join (select job ,DATE_FORMAT(date,'%Y-%m') as second_year_mon, sum(num) as second_year_cnt from resume_info where date between '2026-01-01' and '2026-12-31' group by job,second_year_mon) as b on a.job=b.job and right(a.first_year_mon,2)=right(b.second_year_mon,2) order by a.first_year_mon desc,job desc;