Sqlite 解法, 执行用时: 11ms, 内存消耗: 3364KB, 提交时间: 2022-01-29

select job ,
('2025-'||mon) first_year_mon,sum(case when substr(date,1,4)='2025' then num else 0 end),
('2026-'||mon) second_year_mon,sum(case when substr(date,1,4)='2026' then num else 0 end)
from
(select id,job ,date,num,substr(date,6,2) as mon  from resume_info
)a
group by job ,mon
order by first_year_mon desc,job desc

Sqlite 解法, 执行用时: 11ms, 内存消耗: 3384KB, 提交时间: 2022-01-27

select
job
,('2025-'||mon) first_year_mon
,sum(case when substr(date,1,4)='2025' then num else 0 end) first_year_cnt
,('2026-'||mon) second_year_mon
,sum(case when substr(date,1,4)='2026' then num else 0 end) second_year_cnt
from
(
select
id,job,date,num,substr(date,6,2) mon
from resume_info
)a
group by job,mon
order by first_year_mon desc,job desc

Sqlite 解法, 执行用时: 11ms, 内存消耗: 3460KB, 提交时间: 2021-09-09

select
job
,('2025-'||mon) first_year_mon
,sum(case when substr(date,1,4)='2025' then num else 0 end) first_year_cnt
,('2026-'||mon) second_year_mon
,sum(case when substr(date,1,4)='2026' then num else 0 end) second_year_cnt
from
(
select
id,job,date,num,substr(date,6,2) mon
from resume_info
)a
group by job,mon
order by first_year_mon desc,job desc

Sqlite 解法, 执行用时: 11ms, 内存消耗: 3496KB, 提交时间: 2021-12-03

select a.job,first_year_mon,first_year_cnt,
second_year_mon,second_year_cnt
from (
select job,
substr(date,1,7) first_year_mon,
sum(num) first_year_cnt
from resume_info
where substr(date,1,4) = '2025'
group by job,substr(date,1,7)) a
left join (
select job,
substr(date,1,7) second_year_mon,
sum(num) second_year_cnt
from resume_info
where substr(date,1,4) = '2026'
group by job,substr(date,1,7)
)b
on a.job = b.job 
where substr(a.first_year_mon,6,7) = substr(b.second_year_mon,6,7)
order by first_year_mon desc,a.job desc

Sqlite 解法, 执行用时: 11ms, 内存消耗: 3620KB, 提交时间: 2022-01-05

with r1 as
(select job,date,substr(date,1,7) as first_year_mon,sum(num) as first_year_cnt
from resume_info
where date like '%2025%'
group by job,substr(date,1,7)),
r2 as (
select job,date,substr(date,1,7) as second_year_mon,sum(num) as second_year_cnt
from resume_info
where date like '%2026%'
group by job,substr(date,1,7)
)

select r1.job, first_year_mon,first_year_cnt,second_year_mon,second_year_cnt
from r1
left join r2
on r1.job=r2.job and substr(r1.date,6,2)=substr(r2.date,6,2)
order by first_year_mon DESC,r1.job DESC