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

Sqlite

牛客375186148号

select table1.job,
table1.yearmonth as first_year_mon,
table1.totalnumber as first_year_cnt,
table2.yearmonth as second_year_mon,
table2.totalnumber as second_year_cnt
from
(select job,substring(date,1,7) as yearmonth, substring(date,6,2) as month1, sum(num) as totalnumber
from resume_info
where date like '2025%'
GROUP BY job,yearmonth
order by yearmonth desc
) as table1
join
(select job,substring(date,1,7) as yearmonth, substring(date,6,2) as month2, sum(num) as totalnumber
from resume_info
where date like '2026%'
GROUP BY job,yearmonth
order by yearmonth desc
) as table2
on table1.job=table2.job and table1.month1=table2.month2
order by first_year_mon desc,table1.job desc;

发表于 2022-03-29 15:41:56 回复(0)

Sqlite

呆毛儿本呆

select a.job,a.first_year_mon,a.first_year_cnt,
b.second_year_mon,
b.second_year_cnt
from 
(select job,substr(date,1,7) as first_year_mon,
 sum(num) as first_year_cnt
 from resume_info
 where substr(date,1,4)='2025'
group by job,first_year_mon) a 
inner join
 (select job,substr(date,1,7)as second_year_mon,
 sum(num) as second_year_cnt
 from resume_info
 where substr(date,1,4)='2026'
 group by job,second_year_mon) b 
 on a.job=b.job 
 and substr(first_year_mon,5,7)=substr(second_year_mon,5,7)
order by first_year_mon desc,a.job desc

别忘了表连接的第二个条件，月份相等

发表于 2022-03-18 17:08:37 回复(0)

Sqlite

GeekGuy

select a.job, a.first_year_mon, a.first_year_cnt, b.second_year_mon,b.second_year_cnt
FROM
(select job, STRFTIME('%Y-%m', date)as 'first_year_mon', sum(num)as 'first_year_cnt',
strftime('%m',date) as mon
from resume_info
where date LIKE '2025%'
group by job, first_year_mon)a
JOIN
(select job, STRFTIME('%Y-%m', date)as 'second_year_mon', sum(num)as 'second_year_cnt',
strftime('%m',date) as mon
from resume_info
where date LIKE '2026%'
group by job, second_year_mon)b
on a.job=b.job AND a.mon=b.mon

order by first_year_mon DESC, second_year_mon Desc, a.job DESC

最后的重复项一定要放mon因为不然就会出现重复的月份

发表于 2022-01-21 16:35:03 回复(0)

Sqlite

不停感叹的老林

--sqlite
SELECT
    job,
    min(yr || '-' || mon) AS first_year_mon,
    max(
        CASE
            WHEN yr = '2025' THEN suming
            ELSE 0
        END
    ) AS first_year_cnt,
    max(yr || '-' || mon) AS second_year_mon,
    max(
        CASE
            WHEN yr = '2026' THEN suming
            ELSE 0
        END
    ) AS second_year_cnt
FROM
    (
        SELECT
            job,
            sum(num) AS suming,
            substring(
                cast(date AS char),
                1,
                4
            ) AS yr,
            substring(
                cast(date AS char),
                6,
                2
            ) AS mon
        FROM
            resume_info
        WHERE
            substring(
                cast(date AS char),
                1,
                4
            ) IN('2025', '2026')
        GROUP BY
            job,
            yr,
            mon
    ) AS ri2
GROUP BY
    job,
    mon
ORDER BY
    first_year_mon DESC,
    job DESC;

--mysql
SELECT
    job,
    min(concat(cast(yr AS char), '-', mon)) AS first_year_mon,
    max(
        CASE
            WHEN yr = 2025 THEN suming
            ELSE 0
        END
    ) AS first_year_cnt,
    max(concat(cast(yr AS char), '-', mon)) AS second_year_mon,
    max(
        CASE
            WHEN yr = 2026 THEN suming
            ELSE 0
        END
    ) AS second_year_cnt
FROM
    (
        SELECT
            job,
            sum(num) AS suming,
            extract(
                year
                FROM
                    date
            ) AS yr,
            substring(
                cast(date AS char)
                FROM
                    6 FOR 2
            ) AS mon
        FROM
            resume_info
        WHERE
            extract(
                year
                FROM
                    date
            ) IN(2025, 2026)
        GROUP BY
            job,
            yr,
            mon
    ) AS ri2
GROUP BY
    job,
    mon
ORDER BY
    first_year_mon DESC,
    job DESC;

--postgres
SELECT
    job,
    min(cast(yr AS varchar) || '-' || mon) AS first_year_mon,
    max(
        CASE
            WHEN yr = 2025 THEN suming
            ELSE 0
        END
    ) AS first_year_cnt,
    max(cast(yr AS varchar) || '-' || mon) AS second_year_mon,
    max(
        CASE
            WHEN yr = 2026 THEN suming
            ELSE 0
        END
    ) AS second_year_cnt
FROM
    (
        SELECT
            job,
            sum(num) AS suming,
            extract(
                year
                FROM
                    date
            ) AS yr,
            substring(
                cast(date AS varchar)
                FROM
                    6 FOR 2
            ) AS mon
        FROM
            resume_info
        WHERE
            extract(
                year
                FROM
                    date
            ) IN(2025, 2026)
        GROUP BY
            job,
            yr,
            mon
    ) AS ri2
GROUP BY
    job,
    mon
ORDER BY
    first_year_mon DESC,
    job DESC;

发表于 2022-01-10 22:06:14 回复(0)

Sqlite

小张今天摆烂了吗？

// 合并两个表格

select distinct s.job,s.first_year_mon,s.first_year_cnt,s2.second_year_mon,s2.second_year_cnt

from

( // 查出s.job,s.first_year_mon,s.first_year_cnt 数据

select ri.job,substr(ri.date,1,7) as first_year_mon,sum( ri.num) as first_year_cnt

from resume_info as ri
where ri.date>='2025-01-01' and ri.date<='2025-12-31'
group by ri.job,substr( ri.date,1,7)
order by substr( ri.date,1,7) desc, ri.job desc) as s
join
(// 查出s2.second_year_mon,s2.second_year_cnt数据
select ri1.job,substr(ri1.date,1,7) as second_year_mon,sum( ri1.num) as second_year_cnt
from resume_info as ri1
where ri1.date>='2026-01-01' and ri1.date<='2026-12-31'
group by ri1.job,substr( ri1.date,1,7)
order by substr( ri1.date,1,7) desc, ri1.job desc

)as s2

// 两张表连接的条件，

//对应的job相等，

//两张表的月份相等

on s.job=s2.job
and substr(s.first_year_mon,6,2)=substr(s2.second_year_mon,6,2)

发表于 2021-09-01 20:20:29 回复(0)

Sqlite

如果就这样

select t1.job,t1.first_year_mon,t1.first_year_cnt,t2.second_year_mon,t2.second_year_cnt
from (select job,strftime('%Y-%m',date)as first_year_mon,strftime('%m',date)as mon1,sum(num) as first_year_cnt
from resume_info
where date>='2025-01-01' and date<='2025-12-31'
group by job,first_year_mon) t1
left join (select job,strftime('%Y-%m',date) as second_year_mon,strftime('%m',date) as mon2,sum(num) as second_year_cnt
from resume_info
where date>='2026-01-01' and date<='2026-12-31'
group by job,second_year_mon) t2 on t1.job=t2.job and t1.mon1=t2.mon2
order by t1.first_year_mon desc,t1.job desc

发表于 2021-08-20 11:25:42 回复(0)

Sqlite

李太白10086

select a.*,b.mon2,b.cnt2
from 
(select job,substr(date,1,7) as mon1,sum(num) cnt1
from resume_info
where substr(date,1,4)='2025'
group by mon1,job) a
left join 
(select job,substr(date,1,7) as mon2,sum(num) cnt2
from resume_info
where substr(date,1,4)='2026'
group by mon2,job) b 
on substr(a.mon1,6,2)=substr(b.mon2,6,2)
and a.job=b.job
order by a.mon1 desc,a.job desc

发表于 2021-08-09 14:49:07 回复(0)

Sqlite

英俊的大老虎孤寡孤寡

SELECT b.job,b.mon AS first_year_mon,SUM(b.num) AS first_year_cnt,substr(DATE(b.date,'+1 year'),1,7) AS second_year_mon,
(
    /*3.寻找当前月份的下一年，并且与当前职位相同的投递数量之和*/
    SELECT SUM(c.num)
    FROM resume_info AS c
    WHERE c.job=b.job AND substr(c.date,1,7)=substr(DATE(b.date,'+1 year'),1,7)
) AS second_year_cnt
FROM (
    /*1.找到2025年内各月的每个职位的投递数量*/
    SELECT a.*,substr(date, 1,7) AS mon
    FROM resume_info AS a
    WHERE a.date>='2025-01-01' AND a.date<='2025-12-31'
) AS b
/*2.按照职位和月份分组*/
GROUP BY b.job,b.mon
ORDER BY first_year_mon DESC,b.job DESC

发表于 2021-08-06 10:49:00 回复(0)

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

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

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

输入

输出

问题信息

热门推荐

通过挑战的用户

相关试题