SQL实战86.实习广场投递简历分析(三)

题目

解答

方法一：

select a.job, a.mon as first_year_mon,a.cnt as first_year_cnt,b.mon as second_year_mon,b.cnt as second_year_cnt
 from (
       select job,date_format(date,"%Y-%m") as mon,sum(num) as cnt
			 from   resume_info
			 where date_format(date,"%Y") = "2025"
			 group by job,date_format(date,"%Y-%m") ) as a
, ( select job,date_format(date,"%Y-%m") as mon,sum(num) as cnt
						 from   resume_info
						 where date_format(date,"%Y") = "2026"
						 group by job,date_format(date,"%Y-%m")
						 ) as b
 where  a.job = b.job
 and right(a.mon,2)=right(b.mon,2)   # 同一个月
 order by a.mon desc,job desc;

方法二：

with tmp as (   select job,date_format(date,'%Y-%m') as month,sum(num) as cnt
                from resume_info
                where date between "2025-01-01" and "2026-12-31" 
                group by job,month )

select a.job,a.first_year_mon,a.first_year_cnt,b.second_year_mon,b.second_year_cnt
from (  select job,month as first_year_mon,cnt as first_year_cnt
        from tmp
        where month like "2025%" ) a
left join (  select job,month as second_year_mon,cnt as second_year_cnt
             from tmp
             where month like "2026%" ) 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,a.job desc;

with tmp as (
    select job,substr(date,1,7) as mon,sum(num) as cnt
    from resume_info
    where date between "2025-01-01" and "2026-12-31"
    group by job,mon ) 
    
select a.job,a.mon,a.cnt,b.mon,b.cnt
from (
    select job,mon,cnt
    from  tmp
    where mon like "2025%") a 
left join(
    select job,mon,cnt
    from  tmp
    where mon like "2026%") b
on a.job = b.job
and right(a.mon,2) = right(b.mon,2)
order by a.mon desc,a.job desc;