题解 | #牛客的课程订单分析(六)#

问题描述：请你写出SQL语句查询在2025年内投递简历的每个岗位，每一个月内收到简历的数量，并且按先按月份降序排序，再按简历数目降序排序
图片说明
思路：首先设计筛选条件的语句

SELECT 
FROM resume_info
WHERE YEAR(date) = 2025 /*(或者 WHERE date >='2025-01-01' AND date <= '2025-12-31')*/
GROUP BY job,mon

方案1：采用DATE_FORMA()函数

SELECT job,DATE_FORMAT(date,'%Y-%m') AS mon,SUM(num) AS cnt
FROM resume_info
WHERE date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY job,mon
ORDER BY mon DESC,cnt DESC;

DATE_format(date,format):按照format字符串格式返回格式化后的date
比如本文date_format(date,'%Y-%m')就会将date以"YYYY-MM"的形式返回
具体更多的format格式可参考官方文档：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

方案2：采用字符串截取函数LEFT或SUBSTR,这里建议先采用LTRIM()去除左边的空格后再截取，比较严谨

select job, left(LTRIM(date),7) mon, sum(num) cnt
from resume_info
where year(date)=2025
group by job,mon
order by mon desc,cnt desc

SELECT job,SUBSTR(LTRIM(date),1,7) AS mon,SUM(num) AS cnt
FROM resume_info
WHERE year(date) = 2025
GROUP BY job,mon
ORDER BY mon DESC,cnt DESC;

关于字符串截取函数LEFT,RIGHT,SUNSTR可参考https://blog.nowcoder.net/n/f04557b16d0341eaaae07d6aaf792dbb
LTRIM,RTRIM,TRIM的相关内容可参考https://blog.nowcoder.net/n/2fe6dbedc2414b129169e65ee06d37bf