首页 > 试题广场 >

实习广场投递简历分析(三)

[编程题]实习广场投递简历分析(三)

热度指数：86618 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

在牛客实习广场有很多公司开放职位给同学们投递，同学投递完就会把简历信息存到数据库里。

现在有简历信息表(resume_info)，部分信息简况如下:

id	job	date	num
1	C++	2025-01-02	53
2	Python	2025-01-02	23
3	Java	2025-01-02	12
4	C++	2025-01-03	54
5	Python	2025-01-03	43
6	Java	2025-01-03	41
7	Java	2025-02-03	24
8	C++	2025-02-03	23
9	Python	2025-02-03	34
10	Java	2025-02-04	42
11	C++	2025-02-04	45
12	Python	2025-02-04	59
13	C++	2026-01-04	230
14	Java	2026-01-04	764
15	Python	2026-01-04	644
16	C++	2026-01-06	240
17	Java	2026-01-06	714
18	Python	2026-01-06	624
19	C++	2026-01-04	260
20	Java	2026-02-14	721
21	Python	2026-02-14	321
22	C++	2026-02-14	134
23	Java	2026-02-24	928
24	Python	2026-02-24	525
25	C++	2027-02-06	231

第1行表示，在2025年1月2号，C++岗位收到了53封简历

......

最后1行表示，在2027年2月6号，C++岗位收到了231封简历

请你写出SQL语句查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示，以上例子查询结果如下:

job	first_year_mon	first_year_cnt	second_year_mon	second_year_cnt
Python	2025-02	93	2026-02	846
Java	2025-02	66	2026-02	1649
C++	2025-02	68	2026-02	394
Python	2025-01	66	2026-01	1268
Java	2025-01	53	2026-01	1478
C++	2025-01	107	2026-01	470

解析:

第1行表示Python岗位在2025年2月收到了93份简历，在对应的2026年2月收到了846份简历

......

最后1行表示C++岗位在2025年1月收到了107份简历，在对应的2026年1月收到了470份简历

示例1

输入

drop table if exists resume_info;
CREATE TABLE resume_info (
id int(4) NOT NULL,
job varchar(64) NOT NULL,
date date NOT NULL,
num int(11) NOT NULL,
PRIMARY KEY (id));

INSERT INTO resume_info VALUES
(1,'C++','2025-01-02',53),
(2,'Python','2025-01-02',23),
(3,'Java','2025-01-02',12),
(4,'C++','2025-01-03',54),
(5,'Python','2025-01-03',43),
(6,'Java','2025-01-03',41),
(7,'Java','2025-02-03',24),
(8,'C++','2025-02-03',23),
(9,'Python','2025-02-03',34),
(10,'Java','2025-02-04',42),
(11,'C++','2025-02-04',45),
(12,'Python','2025-02-04',59),
(13,'C++','2026-01-04',230),
(14,'Java','2026-01-04',764),
(15,'Python','2026-01-04',644),
(16,'C++','2026-01-06',240),
(17,'Java','2026-01-06',714),
(18,'Python','2026-01-06',624),
(19,'C++','2026-02-14',260),
(20,'Java','2026-02-14',721),
(21,'Python','2026-02-14',321),
(22,'C++','2026-02-24',134),
(23,'Java','2026-02-24',928),
(24,'Python','2026-02-24',525),
(25,'C++','2027-02-06',231);

输出

Python|2025-02|93|2026-02|846
Java|2025-02|66|2026-02|1649
C++|2025-02|68|2026-02|394
Python|2025-01|66|2026-01|1268
Java|2025-01|53|2026-01|1478
C++|2025-01|107|2026-01|470

算法知识视频讲解

Mysql

健谈的马来熊等oc

这道题为什么大家都是用内连接取交集啊？理论上应该去并集才对吧？因为可能存在某年某个月简历数为0，但是另一年的对应月份有简历

with a0 as(

select distinct job,date_format(date,'%m') as date_mon

from resume_info

a1 as(

select job,date_format(date,'%Y-%m') as first_year_mon,sum(num) as first_year_cnt,date_format(date,'%m') as first_mon

from resume_info

where date_format(date,'%Y')=2025

group by job,first_year_mon,first_mon

a2 as(

select job,date_format(date,'%Y-%m') as second_year_mon,sum(num) as second_year_cnt,date_format(date,'%m') as second_mon

from resume_info

where date_format(date,'%Y')=2026

group by job,second_year_mon,second_mon)

select distinct a0.job,if(first_year_mon is not null,first_year_mon,concat('2025-',a0.date_mon)) as first_year_mon , if(first_year_cnt is not null,first_year_cnt,0) as first_year_cnt,if(second_year_mon is not null,second_year_mon,concat('2026-',a0.date_mon)) as second_year_mon , if(second_year_cnt is not null,second_year_cnt,0) as second_year_cnt

from a0

left join a1

on a0.job=a1.job and a0.date_mon=first_mon

left join a2

on a0.job=a2.job and a0.date_mon=second_mon

order by first_year_mon desc,job desc

发表于 2025-05-05 21:39:54 回复(0)

Mysql

周小果2025

select t1.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 year(date)='2025'

group by job,substr(date,1,7) ) t1

left join(select job,substr(date,1,7) second_year_mon,sum(num) second_year_cnt from resume_info where year(date)='2026'

group by job,substr(date,1,7) ) t2

on substr(t1.first_year_mon,6,2)=substr(t2.second_year_mon,6,2) and t1.job=t2.job

order by first_year_mon desc,job desc

发表于 2025-04-29 16:28:34 回复(0)

Mysql

kukuju

--分别选出2025 和2026表格 join连接

with t as(

select job, date, num,

left(date,7) as year_mon,

year(date) as `year`,

month(date)as mon

from resume_info

)

,t1 as(

select job, year_mon as first_year_mon ,sum(num) as first_year_cnt

from t

where year=2025

group by job, first_year_mon

)

,t2 as(

select job, year_mon as second_year_mon ,sum(num) as second_year_cnt

from t

where year=2026

group by job , second_year_mon

)

select t1.job,first_year_mon,first_year_cnt,second_year_mon,

second_year_cnt

from t1

left join t2

on t1.job=t2.job

where right(first_year_mon,2)=right(second_year_mon,2)

order by first_year_mon desc,job desc

发表于 2025-04-09 18:21:36 回复(0)

Mysql

牛客611160964号

with
    k as (
        SELECT
            job,
            DATE_FORMAT (date, '%Y-%m') AS first_year_mon,
            month (date) mon,
            sum(num) first_year_cnt
        FROM
            resume_info
        where
            year (date) = 2025
        group by
            job,
            month (date),
            DATE_FORMAT (date, '%Y-%m')
    ),
    k1 as (
        SELECT
            k.job,
            first_year_mon,
            first_year_cnt,
            DATE_FORMAT (a.date, '%Y-%m') AS second_year_mon,
            month(a.date) mon1,
            sum(a.num) second_year_cnt
        FROM
            resume_info a
        right join k on k.job=a.job and k.mon=month(a.date)
        where
            year (a.date) = 2026
        group by
            k.job,
            k.mon,
            first_year_mon,
            DATE_FORMAT (a.date, '%Y-%m'),
            month(a.date)
    )
    SELECT job,first_year_mon,first_year_cnt, second_year_mon,second_year_cnt from k1
    order by first_year_mon desc,job desc

发表于 2025-04-08 15:05:11 回复(0)

Mysql

海边的微风

with t1 as(
    select job, date_format(date, '%Y-%m') as first_year_mon, sum(num) as first_year_cnt,
    row_number() over(order by date_format(date, '%Y-%m') desc,job desc) as rk
    from resume_info
    where date between '2025-01-01' and '2025-12-31'
    group by job,first_year_mon

),
t2 as(
    select job, date_format(date, '%Y-%m') as second_year_mon, sum(num) as second_year_cnt,
    row_number() over(order by date_format(date, '%Y-%m') desc,job desc) as rk
    from resume_info
    where date between '2026-01-01' and '2026-12-31'
    group by job,second_year_mon
)

select a.job, first_year_mon, first_year_cnt, second_year_mon, second_year_cnt
from t1 as a
inner join t2 as b using(rk)

发表于 2025-03-17 14:31:29 回复(0)

Mysql

内向的华夫饼

with t01 as (

select job as job,

month(date) as mon,

year(date) as year,

DATE_FORMAT(date, '%Y-%m') as year_mon,

num as num

from resume_info

where year(date) = 2025 or year(date) = 2026

t02 as (

select job as job,

year_mon as first_year_mon,

mon as mon,

sum(num) as first_year_cnt

from t01

where year = 2025

group by job,year_mon,mon

t03 as (

select job as job,

year_mon as second_year_mon,

mon as mon,

sum(num) as second_year_cnt

from t01

where year = 2026

group by job,year_mon,mon

)

select a.job as job,

a.first_year_mon as first_year_mon,

a.first_year_cnt as first_year_cnt,

b.second_year_mon as second_year_mon,

b.second_year_cnt as second_year_cnt

from t02 a

left join t03 b

on a.job = b.job and a.mon = b.mon

order by first_year_mon desc,job desc;

发表于 2025-03-13 00:20:28 回复(0)

Mysql

牛客169856390号

用substring函数

SELECT
	t1.job,
	t1.first_year_mon,
	t1.first_year_cnt,
	t2.second_year_mon,
	t2.second_year_cnt 
FROM
	(
		SELECT
			job,
			SUBSTRING( DATE, 1, 7 ) AS first_year_mon,
			sum( num ) AS first_year_cnt 
		FROM
			resume_info 
		WHERE
			SUBSTRING( DATE, 1, 4 ) = 2025 
		GROUP BY
			job,
			first_year_mon 
		ORDER BY
			first_year_mon DESC,
			job DESC 
	) t1
	LEFT JOIN (
		SELECT
			job,
			SUBSTRING( DATE, 1, 7 ) AS second_year_mon,
			sum( num ) AS second_year_cnt 
		FROM
			resume_info 
		WHERE
			SUBSTRING( DATE, 1, 4 ) = 2026 
		GROUP BY
			job,
			second_year_mon 
		ORDER BY
			second_year_mon DESC,
			job DESC 
	) t2 ON t1.job = t2.job 
where substring(t1.first_year_mon, 6, 7) = substring(t2.second_year_mon, 6, 7)

发表于 2025-02-20 18:32:43 回复(0)

Mysql

coocha

with month_data as

(

select job

,substr(info.date,1,7) as year_mon

,sum(info.num) as mon_cnt

from resume_info info

group by job,substr(info.date,1,7)

)

select d1.job

,d1.year_mon as first_year_mon

,d1.mon_cnt as first_year_cnt

,d2.year_mon as second_year_mon

,d2.mon_cnt as second_year_cnt

from month_data d1

left join month_data d2

on d1.job = d2.job

and substr(d1.year_mon,1,4)+1 = substr(d2.year_mon,1,4)

and substr(d1.year_mon,6,2) = substr(d2.year_mon,6,2)

where substr(d1.year_mon,1,4) = '2025'

order by first_year_mon desc,job desc

发表于 2024-11-28 09:16:00 回复(0)

Mysql

Meraki_Voorpret

用的窗口函数但是不知道错哪了

select a.job, a.first_year_mon, a.first_year_cnt, b.second_year_mon, b.second_year_cnt
from (
    # 2025
    select distinct job, substring(date, 1, 7) as first_year_mon, sum(num) over(partition by job, substring(date, 1, 7) order by substring(date, 1, 7) desc) as first_year_cnt
    from resume_info
    where year(date) = 2025
) as a 
join (
    # 2026
    select distinct job, substring(date, 1, 7) as second_year_mon, sum(num) over(partition by job, substring(date, 1, 7) order by substring(date, 1, 7) desc) as second_year_cnt
    from resume_info
    where year(date) = 2026
) as b on a.job = b.job and date_sub(b.second_year_mon, interval 1 year) = a.first_year_mon
order by a.first_year_mon desc, a.job desc

发表于 2024-11-26 10:32:57 回复(0)

Mysql

牛客621940403号

--提取2025年資料

with r1 as(

select
job,
date_format(date , '%Y-%m') AS first_year_mon,
sum(num) as first_year_cnt
from resume_info
where year(date) = 2025
group by job, first_year_mon
), r2 as(--提取2026年資料
select
job,
date_format(date , '%Y-%m') as second_year_mon,
sum(num) as second_year_cnt
from resume_info
where year(date) = 2026
group by job, second_year_mon
)
--將2026年資料加入到2025年資料的旁邊
select
r1.job,
r1.first_year_mon,
r1.first_year_cnt,
r2.second_year_mon,
r2.second_year_cnt
from r1,r2
where left(r1.first_year_mon,4) + 1 = left(r2.second_year_mon,4)
and right(r1.first_year_mon,2) = right(r2.second_year_mon,2)
and r1.job = r2.job
order by r1.first_year_mon desc , r1.job desc

发表于 2024-11-19 11:43:29 回复(0)

Mysql

lem328

select fy.job,

fy.mon,

fy.cnt,

sy.mon,

sy.cnt

from (select a.job,a.mon,

sum(a.num) as cnt,

concat(a.job,right(a.mon,2)) as id

from (select *,

date_format(date,'%Y-%m') as mon

from resume_info) as a

where year(date)=2025

group by a.job,a.mon) as fy

join

(select b.job,b.mon,

sum(b.num) as cnt,

concat(b.job,right(b.mon,2)) as id

from (select *,

date_format(date,'%Y-%m') as mon

from resume_info) as b

where year(date)=2026

group by b.job,b.mon) as sy

on fy.id = sy.id

order by fy.mon desc,job desc

发表于 2024-11-12 18:29:38 回复(0)

Mysql

年轻的鲸鱼在春招

选出2025年及2026年每个月的数据，做两个表之间的连接

select t1.job,t1.date1 first_year_mon,t1.cnt1 first_year_cnt,
       t2.date1 second_year_mon,t2.cnt1 second_year_cnt
from (
    select job,date1,months,sum(num) cnt1
    from (
            select job,date_format(date,"%Y-%m") date1,
                    num,month(date) as months
            from resume_info
            where year(date)=2025 ) t
    group by job,date1,months
     ) as t1
left join (
    select job,date1,months,sum(num) cnt1
    from (
            select job,date_format(date,"%Y-%m") date1,num,month(date) as months
            from resume_info
            where year(date)=2026 ) t
    group by job,date1,months
     ) as t2
on t1.job = t2.job and t1.months = t2.months
order by t1.date1 desc,t1.job desc

发表于 2024-10-22 20:01:21 回复(0)

Mysql

牛客440842856号

select t1.job,date1 as first_year_mon,first_year_cnt,date2 as second_year_mon,second_year_cnt from

(select job,DATE_FORMAT(date, '%Y-%m') as date1,sum(num) as first_year_cnt from resume_info

where year(date)=2025

group by job,date1) as t1

inner join

(select job,DATE_FORMAT(date, '%Y-%m') as date2,sum(num) as second_year_cnt from resume_info

where year(date)=2026

group by job,date2) as t2

on t1.job=t2.job

and substring(t2.date2,6,2)=substring(t1.date1,6,2)

order by first_year_mon desc,t1.job desc;

发表于 2024-10-18 17:35:25 回复(0)

Mysql

牛客370141165号

WITH t as (SELECT job,mon AS first_year_mon,cnt AS first_year_cnt,

lead(mon,2)OVER(partition by job order by mon) AS second_year_mon,

lead(cnt,2)OVER(partition by job order by mon) AS second_year_cnt

FROM(

SELECT job, date_format(date,"%Y-%m") AS mon,SUM(num) AS cnt

FROM resume_info

WHERE YEAR(date) in (2025,2026)

group by job,mon

ORDER BY job) AS a)

SELECT * FROM t

where first_year_mon LIKE "2025%"

ORDER BY first_year_mon desc ,job DESC

发表于 2024-10-16 21:26:22 回复(0)

Mysql

我对象的健身教练叫波比

With t1 as

(SELECT

job

,date_format(date,'%Y-%m') as year_mon

,sum(num) as cnt

,date_format(date_add(date,interval 1 year),'%Y-%m') as second_year_mon

FROM resume_info

WHERE year(date) = 2025

GROUP BY job, year_mon, second_year_mon

t2 as

(SELECT

job

,date_format(date,'%Y-%m') as year_mon

,sum(num) as cnt

FROM resume_info

WHERE year(date) = 2026

GROUP BY job, year_mon

)

SELECT

t1.job as job

,t1.year_mon as first_year_mon

,t1.cnt as first_year_cnt

,t1.second_year_mon as second_year_mon

,t2.cnt as second_year_cnt

FROM t1

LEFT JOIN t2

ON t1.second_year_mon = t2.year_mon

AND t1.job = t2.job

ORDER BY first_year_mon desc, job desc

发表于 2024-10-08 20:17:29 回复(0)

Mysql

yuciaaa

select
a.job,
a.date,
a.num,
b.date,
b.num
from 
(
select job,
date_format(date, '%Y-%m') as date,
month(date) as month,
sum(num) as num
from resume_info
where year(date) = 2025
group by job, date_format(date, '%Y-%m'),month(date)
order by month(date) desc, job desc
)a 
left join 
(
select job,
date_format(date, '%Y-%m') as date,
month(date) as month,
sum(num) as num
from resume_info
where year(date) = 2026
group by job, date_format(date, '%Y-%m'),month(date)
order by month(date) desc, job desc
)b on a.job = b.job
and a.month = b.month

发表于 2024-09-15 21:28:19 回复(0)

Mysql

Sunny_G

重点应该是join和日期提取这一块，直接把所有需要的信息先提取出来会方便一些啦

select t1.job, t1.ym as fym, t1.cnt as fcnt, t2.ym as sym, t2.cnt as scnt
from (
    select job, left(date, 7) as ym, year(date) as y, month(date) as mon, sum(num) as cnt
    from resume_info
    where 
    date >= '2025-01-01' and date < '2026-01-01'
    group by job, left(date, 7), year(date), month(date)
) t1 left join (
    select job, left(date, 7) as ym, year(date) as y, month(date) as mon, sum(num) as cnt
    from resume_info
    group by job, left(date, 7), year(date), month(date)
) t2 on t1.y = t2.y-1 and t1.mon = t2.mon and t1.job = t2.job
order by t1.mon desc, t1.job desc

发表于 2024-08-14 17:17:14 回复(0)

Mysql

WeiJingsheng

select t1.job as job,
first_year_mon,
first_year_cnt,
second_year_mon,
second_year_cnt
from
(
select job,
date_format(date, '%Y-%m') as first_year_mon,
sum(num) as first_year_cnt
from resume_info
where year(date)=2025
group by job, first_year_mon
) as t1
inner join
(
select job,
date_format(date, '%Y-%m') as second_year_mon,
sum(num) as second_year_cnt
from resume_info
where year(date)=2026
group by job, second_year_mon
) as t2
on t1.job=t2.job and right(t1.first_year_mon, 2)=right(t2.second_year_mon, 2)
order by first_year_mon desc, job desc;

发表于 2024-07-29 21:25:37 回复(0)

Mysql

牛客307154737号

SELECT

aa.job,
aa.date1,
SUM(aa.num) AS first_year_cnt,
bb.date2,
SUM(bb.num) AS second_year_cnt
FROM
(
SELECT
job,
date,
LEFT(date, 7) AS date1,
LEFT(date_add(date, INTERVAL 1 YEAR), 7) AS date2
FROM
resume_info
WHERE
YEAR(date) = 2025
) aa
INNER JOIN
(
SELECT
job,
num,
LEFT(date, 7) AS date2
FROM
resume_info
WHERE
YEAR(date) = 2026
) bb
ON aa.date2 = bb.date2 AND aa.job = bb.job
GROUP BY
aa.job, aa.date1, bb.date2
ORDER BY
aa.date1 DESC, aa.job DESC;
大佬帮忙看戏这个里面输出的结果为啥是正确结果*2

发表于 2024-07-23 11:09:53 回复(0)