题解 | #实习广场投递简历分析(三)#
实习广场投递简历分析(三)
http://www.nowcoder.com/practice/83f84aa5c32b4cf5a75558d02dd7743c
先做一个查询查出来2025年的，在关联上一个查询2026 条件就是 job相等 月份相同
里面涉及几个函数时间和字符串的
- EXTRACT(year from date)='2025' :这个是获取date类型的年份 也可以用 year(date)
- date_format(date,'%Y-%m') :date类型转换成字符串， %Y-%m 一定是大写的Y 不然就会只获取到年份的最后俩各位
- SUBSTR(t.date_month,6) ：字符串截取 位置从1开始的 这个是从第6个字符 截取到最后
with temp as
(
SELECT
job,
date_format(date,'%Y-%m') date_month,
sum(num) as num
FROM
resume_info
WHERE
EXTRACT(year from date)='2025'
group BY
date_month,job
order by
date_month desc,job desc
)
SELECT
t.job,
t.date_month as first_year_mon,
t.num as first_year_cnt,
t1.date_month as second_year_mon,
t1.num as second_year_cnt
FROM
temp t
left JOIN
(
SELECT
job,
date_format(date,'%Y-%m') date_month,
sum(num) as num
FROM
resume_info
WHERE
EXTRACT(year from date)='2026'
group BY
date_month,job
order by
date_month desc,job desc
) t1
ON t.job=t1.job and SUBSTR(t.date_month,6)=SUBSTR(t1.date_month,6)
