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

select
b.job
,b.fm
,b.fs
,c.sm
,c.ss
from(
select
a.job
,a.mon fm
,sum(a.num) fs
from (
select
id
,job
,substring(date,1,7) mon
,num
from resume_info
where date >= '2025-01-01'
and date <= '2025-12-31'
) a
group by 1,2
order by a.mon desc,fm desc
) b
join(
select
a.job
,a.mon sm
,sum(a.num) ss
from (
select
id
,job
,substring(date,1,7) mon
,num
from resume_info
where date >= '2026-01-01'
and date <= '2026-12-31'
) a
group by 1,2
order by a.mon desc,sm desc
) c
on b.job = c.job
and substring(b.fm,6,2) = substring(c.sm,6,2)
order by b.fm desc,b.job desc