题解 | #实习广场投递简历分析(三)#
实习广场投递简历分析(三)
http://www.nowcoder.com/practice/83f84aa5c32b4cf5a75558d02dd7743c
#第一步：格式化日期
#第二步：窗口函数：统计各年月简历数量
#第三步:构造第一个年月的订单统计表
#第四步：构造第二个年月的订单统计表
#第五步：将第三步和第四步的表合并
SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info
#第二步：窗口函数：统计各年月简历数量
SELECT DISTINCT job,year_mon, SUM(num) over(PARTITION BY job, year_mon) "cnt" FROM (SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info) t
#第三步:构造第一个年月的订单统计表
SELECT DISTINCT job,CASE WHEN LEFT(year_mon,4) = "2025" THEN year_mon END "first_year_mon", cnt "first_year_cnt" FROM (SELECT DISTINCT job,year_mon, SUM(num) over(PARTITION BY job, year_mon) "cnt" FROM (SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info) t)tt
#第四步：构造第二个年月的订单统计表
SELECT DISTINCT job,CASE WHEN LEFT(year_mon,4)="2026" THEN year_mon END "second_year_mon", cnt "second_year_cnt" FROM (SELECT DISTINCT job,year_mon, SUM(num) over(PARTITION BY job, year_mon) "cnt" FROM (SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info) t)tt
#第五步：将第三步和第四步的表合并
SELECT f1.job, f1.first_year_mon,f1.first_year_cnt, f2.second_year_mon, f2.second_year_cnt FROM (SELECT DISTINCT job,CASE WHEN LEFT(year_mon,4) = "2025" THEN year_mon END "first_year_mon", cnt "first_year_cnt" FROM (SELECT DISTINCT job,year_mon, SUM(num) over(PARTITION BY job, year_mon) "cnt" FROM (SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info) t)tt) f1 JOIN (SELECT DISTINCT job,CASE WHEN LEFT(year_mon,4)="2026" THEN year_mon END "second_year_mon", cnt "second_year_cnt" FROM (SELECT DISTINCT job,year_mon, SUM(num) over(PARTITION BY job, year_mon) "cnt" FROM (SELECT id, job, DATE_FORMAT(DATE,"%Y-%m") "year_mon", num FROM resume_info) t)tt) f2 ON f1.job = f2.job WHERE LEFT(f1.first_year_mon,4) = "2025" AND LEFT(f2.second_year_mon,4) = "2026" AND RIGHT(f1.first_year_mon,2) = RIGHT(f2.second_year_mon,2) ORDER BY f1.first_year_mon DESC, f1.job DESC
查看17道真题和解析
