题目 : https://www.nowcoder.com/practice/3c950490e44945fc9848ce43090ba455?tpId=341

数据

drop table if exists deliver_record_detail;

CREATE TABLE `deliver_record_detail` (
`user_id` int NOT NULL,
`job_id` int NOT NULL,
`job_city` varchar(64) NOT NULL,
`device` varchar(14) NOT NULL,
`min_salary` int ,
`max_salary` int,
`deliver_date` date  NOT NULL);

INSERT INTO deliver_record_detail VALUES
(101,14550, '上海','app','18','20', '2020-03-01')
,(102,14550, '北京','pc','10','15','2021-07-07')
,(103,23654, '北京','pc','12',null, '2021-04-09')
,(102,23152, '杭州','app','25','30','2022-03-17')
,(105,75432, '厦门','pc','30','60', '2016-08-15')
,(204,49642, '北京市','pc', null, null, '2019-05-15');

需求

查询城市为北京的职位投递记录

查询结果 :

user_id|job_id|job_city|device|min_salary|max_salary|deliver_date
102|14550|北京|pc|10|15|2021-07-07
103|23654|北京|pc|12|None|2021-04-09
204|49642|北京市|pc|None|None|2019-05-15

解决

注意 :

• 数据中 , 可能会带市

后模糊匹配查询 :

select * 
from deliver_record_detail
where job_city like '北京%';

用 in 查询 :

select *
from deliver_record_detail
where job_city in ('北京' , '北京市');

用 or 查询 :

select *
from deliver_record_detail
where job_city = '北京' or job_city = '北京市';