实习广场投递简历分析(三) left right substring join

with t as(
    select
        job,
        left(date,7) first_year_mon,
        sum(num) first_year_cnt
    from resume_info
    where date like '2025%'
    group by job,first_year_mon
),
t1 as(
    select
        job,
        left(date,7) second_year_mon,
        sum(num) second_year_cnt
    from resume_info
    where date like '2026%'
    group by job,second_year_mon
)

select 
    t.job,
    first_year_mon,
    first_year_cnt,
    second_year_mon,
    second_year_cnt
from t
left join t1 on t.job=t1.job
and right(first_year_mon,2)=right(second_year_mon,2)
order by first_year_mon desc,job desc

left(str,k) 左边前k个字符

right(str,k) 右边后k个字符

substring(str,k,len) 第k个起，长度为len的字符