题目 : https://www.nowcoder.com/practice/3e40ce574a484b64b88781ac0b1340e8?tpId=341
数据
drop table if exists deliver_record;
drop table if exists job_info;
CREATE TABLE `deliver_record` (
`user_id` int NOT NULL, -- 投递用户id
`job_id` int NOT NULL, -- 投递职位ID
`platform` varchar(10) NOT NULL, -- 投递平台
`resume_id` int NOT NULL, -- 投递的简历ID
`resume_if_checked`int NOT NULL, -- 简历是否被查看 1 被查看 0 未被查看
`deliver_date` date NOT NULL); -- 投递日期
CREATE TABLE `job_info` (
`job_id` int NOT NULL, -- 职位id
`boss_id` int NOT NULL, -- hr id
`company_id` int NOT NULL, -- 公司id
`post_time` datetime NOT NULL, -- 职位发布时间
`salary` int, -- 职位工资
`job_city` varchar(32) NOT NULL ); -- 职位城市
INSERT INTO deliver_record VALUES(101, 18903, 'app', 308897, 1, '2021-03-01');
INSERT INTO deliver_record VALUES(102, 21089, 'pc', 154906, 0, '2022-07-07');
INSERT INTO deliver_record VALUES(102, 22869, 'pc', 967389, 1, '2022-04-09');
INSERT INTO deliver_record VALUES(104, 16739, 'app', 327368, 0, '2018-09-17');
INSERT INTO deliver_record VALUES(105, 34992, 'pc', 600367, 0, '2020-11-15');
INSERT INTO deliver_record VALUES(104, 22889, 'pc', 202819, 1, '2022-05-15');
INSERT INTO job_info VALUES(18903, 202, 3, '2021-03-01 11:22:33', 112000, '北京');
INSERT INTO job_info VALUES(21089, 203, 6, '2022-05-09 09:50:34', 78000, '西安');
INSERT INTO job_info VALUES(22869, 204, 2, '2022-03-09 15:10:50', 92000, '上海');
INSERT INTO job_info VALUES(16739, 204, 6, '2018-08-12 10:00:00', 62000, '杭州');
INSERT INTO job_info VALUES(34992, 205, 9, '2020-11-09 22:01:03', 123000, '北京');
INSERT INTO job_info VALUES(22889, 206, 16, '2022-03-09 01:07:09', 150000, '上海');
需求
查询每个公司 company_id
查看过的投递用户数 cnt
resume_if_checked
简历是否被查看 1 , 被查看 0 未被查看
查询结果 :
company_id|cnt
2|1
3|1
16|1
解决
-
先看俩表的关联关系 , 是通过 job_id 关联
-
每个公司查看用户
- 对公司, job_id 进行分组
- 筛选出每个公司的每个Job
select company_id,
job_id
from job_info
group by company_id, job_id;
- 查看过的投递用户
- 通过 resume_if_checked 过滤筛选的用户
select user_id,
job_id
from deliver_record
where resume_if_checked = 1;
- 根据上面描述 , 可以通过job 关联 统计出每个公司的查看用户数
with t1 as (
select job_id,
user_id
from deliver_record
where resume_if_checked = 1
)
select
t2.company_id,
count(user_id) as cnt
from job_info t2 join t1
on t2.job_id = t1.job_id
group by t2.company_id
order by t2.company_id;
方法二 : 子查询 , 进行整合
select
company_id,
count(1) as cnt
from job_info t1 join deliver_record t2
on t1.job_id = t2.job_id
where t2.resume_if_checked = 1
group by company_id
order by company_id;