新建字段作为链接条件

1.在第二题的基础上把year(date) = '2025' 换成year(date) = '2026'
由此可以得到两张作为子查询的基础表 r1, r2
2.按题目要求最外层select语句：
select
r1.job
,r1.mon as first_year_mon
,r1.cnt as first_year_cnt
,r2.mon as second_year_mon
,r2.cnt as second_year_cnt
from（r1, r2）
3.最关键的是找到两个子查询正确的连接条件，如果按r1和r2原有的字段，
按job/date做为链接条件试着运行后均不正确（因为job和date作为索引均不唯一），又试着把id作为链接条件，结果返回空值（菜鸡写代码就是这么麻烦）。
所以想到在r1和r2中添加新字段concat(job,month(date)作为链接条件，然后order by 搞定。

select 
r1.job
,r1.mon as first_year_mon
,r1.cnt as first_year_cnt
,r2.mon as second_year_mon
,r2.cnt as second_year_cnt
from (
   (select concat(job,month(date))as id,job, date_format(date,'%Y-%m') as mon,sum(num) as cnt
        from resume_info 
    where year(date) = '2025'
    group by job,mon) r1,

    (select concat(job,month(date))as id,job, date_format(date,'%Y-%m') as mon,sum(num) as cnt
        from resume_info 
    where year(date) = '2026'
    group by job,mon)r2
    ) 
where r1.id = r2.id
order by r1.mon desc, r1.job desc