题目 : https://www.nowcoder.com/practice/8beaed9f153d4f65acda4db12ae55ddd?tpId=341
数据
drop table if exists job_info;
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 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, '上海');
需求
查询职位发布时间在 2021 年后的 job_id, boss_id, company_id,与职位城市为上海的job_id, boss_id, company_id
- 二者结果合并去重,按 job_city 升序排序
查询结果 :
job_id|boss_id|company_id
22869|204|2
22889|206|16
解决
注意点 :
- union all : 不去重 , union : 去重
- 用 or , 会导致俩个条件都满足 , 但只要一条记录
方法1
用 or
进行去重
- 对俩个条件判断
select job_id,
boss_id,
company_id
from job_info
where year (post_time) >= '2021' or job_city = '上海'
order by job_city;
方法2
用 union
进行去重
- 对表分别判断
with t1 as (
select job_id,
boss_id,
company_id,
job_city
from job_info
where year(post_time) >= '2021'
union
select job_id,
boss_id,
company_id,
job_city
from job_info
where job_city = '上海'
)
select job_id,
boss_id,
company_id
from t1
order by job_city;