文章目录
一、基本查询
【题目1：查询所有投递用户user id并去重】
--输入：
drop table if exists deliver_record;
CREATE TABLE `deliver_record` (
`user_id` int NOT NULL,
`job_id` int NOT NULL,
`device` varchar(14) NOT NULL,
`job_salary` varchar(32) ,
`deliver_date` date NOT NULL);
INSERT INTO deliver_record VALUES(101,14550, 'app','838900', '2020-03-01');
INSERT INTO deliver_record VALUES(102,14550, 'pc','67356','2021-07-07');
INSERT INTO deliver_record VALUES(103,23654, 'pc',null, '2021-04-09');
INSERT INTO deliver_record VALUES(102,23152, 'app','297032','2022-03-17');
INSERT INTO deliver_record VALUES(105,75432, 'pc','156770', '2006-08-15');
select
user_id
from deliver_record
group by user_id
;
【题目2：查询限制行数】
--输入：
drop table if exists deliver_record;
CREATE TABLE `deliver_record` (
`user_id` int NOT NULL,
`job_id` int NOT NULL,
`device` varchar(14) NOT NULL,
`job_salary` varchar(32) ,
`deliver_date` date NOT NULL);
INSERT INTO deliver_record VALUES(101,14550, 'app','838900', '2020-03-01');
INSERT INTO deliver_record VALUES(102,14550, 'pc','67356','2021-07-07');
INSERT INTO deliver_record VALUES(103,23654, 'pc',null, '2021-04-09');
INSERT INTO deliver_record VALUES(102,23152, 'app','297032','2022-03-17');
INSERT INTO deliver_record VALUES(105,75432, 'pc','156770', '2006-08-15');
select
user_id
,job_id
,device
,job_salary
,deliver_date
from deliver_record
limit 2
;
【题目3：将查询列重新命名】
--输入：
drop table if exists deliver_record;
CREATE TABLE `deliver_record` (
`user_id` int NOT NULL,
`job_id` int NOT NULL,
`device` varchar(14) NOT NULL,
`job_salary` varchar(32) ,
`deliver_date` date NOT NULL);
INSERT INTO deliver_record VALUES(101,14550, 'app','838900', '2020-03-01');
INSERT INTO deliver_record VALUES(102,14550, 'pc','67356','2021-07-07');
INSERT INTO deliver_record VALUES(103,23654, 'pc',null, '2021-04-09');
INSERT INTO deliver_record VALUES(102,23152, 'app','297032','2022-03-17');
INSERT INTO deliver_record VALUES(105,75432, 'pc','156770', '2006-08-15');
select
job_salary '职位工资'
from deliver_record
;
【题目4：查询表总行数】
--输入：
drop table if exists deliver_record;
CREATE TABLE `deliver_record` (
`user_id` int NOT NULL,
`job_id` int NOT NULL,
`device` varchar(14) NOT NULL,
`job_salary` varchar(32) ,
`deliver_date` date NOT NULL);
INSERT INTO deliver_record VALUES(101,14550, 'app','838900', '2020-03-01');
INSERT INTO deliver_record VALUES(102,14550, 'pc','67356','2021-07-07');
INSERT INTO deliver_record VALUES(103,23654, 'pc',null, '2021-04-09');
INSERT INTO deliver_record VALUES(102,23152, 'app','297032','2022-03-17');
INSERT INTO deliver_record VALUES(105,75432, 'pc','156770', '2006-08-15');
select
count(*) cnt
from deliver_record
;
二、数据过滤
【题目1：查询在pc上投递的所有投递记录】
--输入：
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');
select * from deliver_record_detail
where device='pc'
;
【题目2：查询投递最低最高薪资差别大于2的职位的投递用户】
--输入：
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');
select
user_id
from deliver_record_detail
where (max_salary-min_salary)>2
;
【题目3**：查询薪资信息不为空的职位投递记录】
--输入：
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');
select * from deliver_record_detail
where min_salary is not null or max_salary is not null
;
【题目4：查询城市为北京的职位投递记录】
--输入：
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');
select * from deliver_record_detail
where job_city like '北京%'
;
三：函数
【题目1：计算总刷题数,并将所选列名改为总刷题数】
--输入：
drop table if exists questions_pass_record_detail;
CREATE TABLE `questions_pass_record_detail` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`pass_count` int NOT NULL,
`date` date NOT NULL);
INSERT INTO questions_pass_record_detail VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record_detail VALUES(102, 'sql', 'pc', 15,'2021-07-07');
INSERT INTO questions_pass_record_detail VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record_detail VALUES(104, 'python', 'app', 3,'2022-03-17');
INSERT INTO questions_pass_record_detail VALUES(105, 'sql', 'pc', 60, '2016-08-15');
INSERT INTO questions_pass_record_detail VALUES(204, 'sql', 'pc', 20, '2019-05-15');
select
sum(pass_count) '总刷题数'
from questions_pass_record_detail
;
【题目2：计算刷题总人数】
--输入：
drop table if exists questions_pass_record_detail;
CREATE TABLE `questions_pass_record_detail` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`pass_count` int NOT NULL,
`date` date NOT NULL);
INSERT INTO questions_pass_record_detail VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record_detail VALUES(102, 'sql', 'pc', 15,'2021-07-07');
INSERT INTO questions_pass_record_detail VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record_detail VALUES(104, 'python', 'app', 3,'2022-03-17');
INSERT INTO questions_pass_record_detail VALUES(105, 'sql', 'pc', 60, '2016-08-15');
INSERT INTO questions_pass_record_detail VALUES(204, 'sql', 'pc', 20, '2019-05-15');
select
count(distinct user_id) cnt
from questions_pass_record_detail
;
【题目3：找出sql类题目的单次最大刷题数】
--输入：
drop table if exists questions_pass_record_detail;
CREATE TABLE `questions_pass_record_detail` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`pass_count` int NOT NULL,
`date` date NOT NULL);
INSERT INTO questions_pass_record_detail VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record_detail VALUES(102, 'sql', 'pc', 15,'2021-07-07');
INSERT INTO questions_pass_record_detail VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record_detail VALUES(104, 'python', 'app', 3,'2022-03-17');
INSERT INTO questions_pass_record_detail VALUES(105, 'sql', 'pc', 60, '2016-08-15');
INSERT INTO questions_pass_record_detail VALUES(204, 'sql', 'pc', 20, '2019-05-15');
select
max(pass_count) maxCnt
from questions_pass_record_detail
where question_type='sql'
;
【题目4：计算单次平均刷题数】
--输入：
drop table if exists questions_pass_record_detail;
CREATE TABLE `questions_pass_record_detail` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`pass_count` int NOT NULL,
`date` date NOT NULL);
INSERT INTO questions_pass_record_detail VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record_detail VALUES(102, 'sql', 'pc', 15,'2021-07-07');
INSERT INTO questions_pass_record_detail VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record_detail VALUES(104, 'python', 'app', 3,'2022-03-17');
INSERT INTO questions_pass_record_detail VALUES(105, 'sql', 'pc', 60, '2016-08-15');
INSERT INTO questions_pass_record_detail VALUES(204, 'sql', 'pc', 20, '2019-05-15');
select
avg(pass_count) avgCnt
from questions_pass_record_detail
;
四：分组聚合
【题目1：统计每天总刷题数】
--输入：
drop table if exists questions_pass_record_detail;
CREATE TABLE `questions_pass_record_detail` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`pass_count` int NOT NULL,
`date` date NOT NULL);
INSERT INTO questions_pass_record_detail VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record_detail VALUES(102, 'sql', 'pc', 15,'2021-07-07');
INSERT INTO questions_pass_record_detail VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record_detail VALUES(104, 'python', 'app', 3,'2022-03-17');
INSERT INTO questions_pass_record_detail VALUES(105, 'sql', 'pc', 60, '2016-08-15');
INSERT INTO questions_pass_record_detail VALUES(204, 'sql', 'pc', 20, '2019-05-15');
select
date days
,sum(pass_count) passCnt
from questions_pass_record_detail
group by date
;
【题目2：统计每天刷题数超过5的user id以及刷题数】
--输入：
drop table if exists questions_pass_record_detail;
CREATE TABLE `questions_pass_record_detail` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`pass_count` int NOT NULL,
`date` date NOT NULL);
INSERT INTO questions_pass_record_detail VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record_detail VALUES(102, 'sql', 'pc', 15,'2021-07-07');
INSERT INTO questions_pass_record_detail VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record_detail VALUES(104, 'python', 'app', 3,'2022-03-17');
INSERT INTO questions_pass_record_detail VALUES(105, 'sql', 'pc', 60, '2016-08-15');
INSERT INTO questions_pass_record_detail VALUES(204, 'sql', 'pc', 20, '2019-05-15');
select
date
,user_id
,sum(pass_count) total_pass_count
from questions_pass_record_detail
where pass_count>5
group by date,user_id
;
【题目3：统计不同类型题目的刷题数，并按刷题数进行升序排列】
--输入：
drop table if exists questions_pass_record_detail;
CREATE TABLE `questions_pass_record_detail` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`pass_count` int NOT NULL,
`date` date NOT NULL);
INSERT INTO questions_pass_record_detail VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record_detail VALUES(102, 'sql', 'pc', 15,'2021-07-07');
INSERT INTO questions_pass_record_detail VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record_detail VALUES(104, 'python', 'app', 3,'2022-03-17');
INSERT INTO questions_pass_record_detail VALUES(105, 'sql', 'pc', 60, '2016-08-15');
INSERT INTO questions_pass_record_detail VALUES(204, 'sql', 'pc', 20, '2019-05-15');
select
question_type
,sum(pass_count) passCnt
from questions_pass_record_detail
group by question_type
order by passCnt
;
五：子查询
【题目1：查询2022年毕业用户的刷题记录】
--输入：
drop table if exists questions_pass_record;
drop table if exists user_info;
CREATE TABLE `questions_pass_record` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`pass_count` int NOT NULL,
`date` date NOT NULL);
CREATE TABLE `user_info` (
`user_id` int NOT NULL,
`graduation_year` int NOT NULL,
`register_time` datetime NOT NULL,
`gender` varchar(14),
`age` int,
`university` varchar(32) NOT NULL );
INSERT INTO questions_pass_record VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record VALUES(102, 'sql', 'pc', 15,'2021-07-07');
INSERT INTO questions_pass_record VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record VALUES(104, 'python', 'app', 3,'2022-03-17');
INSERT INTO questions_pass_record VALUES(105, 'sql', 'pc', 60, '2018-08-15');
INSERT INTO questions_pass_record VALUES(104, 'sql', 'pc', 20, '2019-05-15');
INSERT INTO user_info VALUES(101, 2022, '2021-03-01 11:22:33', 'male', 27, '北京大学');
INSERT INTO user_info VALUES(102, 2023, '2022-05-09 09:50:34', 'female', 31,'清华大学');
INSERT INTO user_info VALUES(103, 2021, '2022-03-09 15:10:50', 'male', null, '复旦大学');
INSERT INTO user_info VALUES(104, 2020, '2018-08-12 10:00:00', null, 23,'墨尔本大学');
INSERT INTO user_info VALUES(105, 2022, '2020-11-09 22:01:03', 'female', 26, '北京大学');
INSERT INTO user_info VALUES(210, 2022, '2022-03-09 01:07:09', 'male', 20, '上海交通大学');
select
user_id
,question_type
,device
,pass_count
,date
from questions_pass_record
where user_id in (
select
user_id
from user_info
where graduation_year='2022'
)
;
【题目2：查询2022年以来刷题用户的用user id和毕业院校】
--输入：
drop table if exists questions_pass_record;
drop table if exists user_info;
CREATE TABLE `questions_pass_record` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`pass_count` int NOT NULL,
`date` date NOT NULL);
CREATE TABLE `user_info` (
`user_id` int NOT NULL,
`graduation_year` int NOT NULL,
`register_time` datetime NOT NULL,
`gender` varchar(14),
`age` int,
`university` varchar(32) NOT NULL );
INSERT INTO questions_pass_record VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record VALUES(102, 'sql', 'pc', 15,'2021-07-07');
INSERT INTO questions_pass_record VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record VALUES(104, 'python', 'app', 3,'2022-03-17');
INSERT INTO questions_pass_record VALUES(105, 'sql', 'pc', 60, '2018-08-15');
INSERT INTO questions_pass_record VALUES(104, 'sql', 'pc', 20, '2019-05-15');
INSERT INTO user_info VALUES(101, 2022, '2021-03-01 11:22:33', 'male', 27, '北京大学');
INSERT INTO user_info VALUES(102, 2023, '2022-05-09 09:50:34', 'female', 31,'清华大学');
INSERT INTO user_info VALUES(103, 2021, '2022-03-09 15:10:50', 'male', null, '复旦大学');
INSERT INTO user_info VALUES(104, 2020, '2018-08-12 10:00:00', null, 23,'墨尔本大学');
INSERT INTO user_info VALUES(105, 2022, '2020-11-09 22:01:03', 'female', 26, '北京大学');
INSERT INTO user_info VALUES(210, 2022, '2022-03-09 01:07:09', 'male', 20, '上海交通大学');
select
user_id
,university
from user_info
where user_id in (
select
user_id
from questions_pass_record
where substr(date,1,4)>='2022'
group by user_id
)
;
六：多表连接
【题目1：查询被投递过的职位信息（答案有问题）】
--输入：
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, '上海');
# 能过的答案
select * from job_info;
# 根据题意解
select
a.company_id
,count(distinct b.user_id) cnt
from (
select
job_id
,company_id
from job_info
) a join (
select
user_id
,job_id
,resume_if_checked
from deliver_record
where resume_if_checked='1'
) b on a.job_id=b.job_id
group by a.company_id
order by company_id
;
【题目2：查询每个公司查看过的投递用户数】
--输入：
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, '上海');
select
a.company_id
,count(distinct b.user_id) cnt
from (
select
job_id
,company_id
from job_info
) a join (
select
user_id
,job_id
,resume_if_checked
from deliver_record
where resume_if_checked='1'
) b on a.job_id=b.job_id
group by a.company_id
;
七：组合查询
【题目1：查询职位城市在北京或者职位工资高于100000的job_id和company_id】
--输入：
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, '上海');
select
job_id
,company_id
from job_info
where job_city='北京'
union all
select
job_id
,company_id
from job_info
where salary>100000
;
【题目2：查询职位发布时间在2021年后或职位城市为上海的job_id, boss_id, company_id】
--输入：
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, '上海');
select
job_id
,boss_id
,company_id
from (
select
job_id
,boss_id
,company_id
,job_city
from job_info
where substr(post_time,1,4)>='2021'
union
select
job_id
,boss_id
,company_id
,job_city
from job_info
where job_city='上海'
) a
order by job_city
;
八：技能专项-case when使用
【题目1：判断其是否有过购买记录】
--输入：
drop table if exists customers_info;
CREATE TABLE `customers_info` (
`customer_id` int NOT NULL, -- 客户id
`gender` varchar(10) NOT NULL, -- 客户性别
`city` varchar(32) NOT NULL, -- 客户所在城市
`country` varchar(32) NOT NULL, -- 客户所在国家
`age` int, -- 客户年龄
`latest_place_order_date` date ); -- 客户最近购买日期
INSERT INTO customers_info VALUES(18903, 'male', '北京', '中国', 23, '2021-03-01');
INSERT INTO customers_info VALUES(21089, 'female', '纽约', '美国', 66, '2022-05-09');
INSERT INTO customers_info VALUES(22869, 'male', '上海', '中国', null, '2022-03-09');
INSERT INTO customers_info VALUES(16739, 'null', '杭州', '中国', 36, null);
INSERT INTO customers_info VALUES(34992, 'female', '北京', '中国', 19, '2020-11-09');
INSERT INTO customers_info VALUES(22889, 'male', '墨尔本', '澳大利亚', 26, null);
select
customer_id
,if(latest_place_order_date is not null,1,0) if_placed_order
from customers_info
;
【题目2：请按城市对客户进行排序,如果城市为空，则按国家排序】
--输入：
drop table if exists customers_info;
CREATE TABLE `customers_info` (
`customer_id` int NOT NULL, -- 客户id
`gender` varchar(10) NOT NULL, -- 客户性别
`city` varchar(32) NOT NULL, -- 客户所在城市
`country` varchar(32) NOT NULL, -- 客户所在国家
`age` int, -- 客户年龄
`latest_place_order_date` date ); -- 客户最近购买日期
INSERT INTO customers_info VALUES(18903, 'male', '北京', '中国', 23, '2021-03-01');
INSERT INTO customers_info VALUES(21089, 'female', '纽约', '美国', 66, '2022-05-09');
INSERT INTO customers_info VALUES(22869, 'male', '上海', '中国', null, '2022-03-09');
INSERT INTO customers_info VALUES(16739, 'null', '杭州', '中国', 36, null);
INSERT INTO customers_info VALUES(34992, 'female', '北京', '中国', 19, '2020-11-09');
INSERT INTO customers_info VALUES(22889, 'male', '墨尔本', '澳大利亚', 26, null);
select
customer_id
,gender
,city
,country
,age
,latest_place_order_date
from customers_info
order by city,country
;
【题目3：分群并计算群体人数】
--输入：
drop table if exists customers_info;
CREATE TABLE `customers_info` (
`customer_id` int NOT NULL, -- 客户id
`gender` varchar(10) NOT NULL, -- 客户性别
`city` varchar(32) NOT NULL, -- 客户所在城市
`country` varchar(32) NOT NULL, -- 客户所在国家
`age` int, -- 客户年龄
`latest_place_order_date` date ); -- 客户最近购买日期
INSERT INTO customers_info VALUES(18903, 'male', '北京', '中国', 23, '2021-03-01');
INSERT INTO customers_info VALUES(21089, 'female', '纽约', '美国', 66, '2022-05-09');
INSERT INTO customers_info VALUES(22869, 'male', '上海', '中国', null, '2022-03-09');
INSERT INTO customers_info VALUES(16739, 'null', '杭州', '中国', 36, null);
INSERT INTO customers_info VALUES(34992, 'female', '北京', '中国', 19, '2020-11-09');
INSERT INTO customers_info VALUES(22889, 'male', '墨尔本', '澳大利亚', 26, null);
select
age_group
,count(customer_id) user_count
from (
select
case when age<20 then '20以下'
when age between 20 and 50 then '20-50'
when age>50 then '50以上'
when age is null then '未填写'
else '其他' end age_group
,customer_id
from customers_info
) a
group by age_group
;
九：多表连接-窗口函数
【题目1：查询每天刷题通过数最多的前二名用户id和刷题数】
--输入：
drop table if exists questions_pass_record;
CREATE TABLE `questions_pass_record` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`pass_count` int NOT NULL,
`date` date NOT NULL);
INSERT INTO questions_pass_record VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record VALUES(102, 'sql', 'pc', 15,'2020-03-01');
INSERT INTO questions_pass_record VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record VALUES(202, 'python', 'pc', 11, '2021-04-09');
INSERT INTO questions_pass_record VALUES(104, 'python', 'app', 3,'2021-04-09');
INSERT INTO questions_pass_record VALUES(105, 'sql', 'pc', 60, '2018-08-15');
INSERT INTO questions_pass_record VALUES(104, 'sql', 'pc', 20, '2018-08-15');
INSERT INTO questions_pass_record VALUES(304, 'sql', 'pc', 10, '2018-08-15');
select
date
,user_id
,pass_count
from (
select
date
,user_id
,pass_count
,row_number() over(partition by date order by pass_count desc) rn
from questions_pass_record
) a
where rn<=2
order by date
;
【题目2：查询用户刷题日期和下一次刷题日期】
--输入：
drop table if exists questions_pass_record;
CREATE TABLE `questions_pass_record` (
`user_id` int NOT NULL,
`question_type` varchar(32) NOT NULL,
`device` varchar(14) NOT NULL,
`question_id` int NOT NULL,
`date` date NOT NULL);
INSERT INTO questions_pass_record VALUES(101, 'java', 'app', 2, '2020-03-01');
INSERT INTO questions_pass_record VALUES(102, 'sql', 'pc', 15,'2021-07-07');
INSERT INTO questions_pass_record VALUES(102, 'python', 'pc', 9, '2021-04-09');
INSERT INTO questions_pass_record VALUES(102, 'python', 'app', 3,'2022-03-17');
INSERT INTO questions_pass_record VALUES(103, 'sql', 'pc', 60, '2018-08-15');
INSERT INTO questions_pass_record VALUES(104, 'sql', 'pc', 20, '2019-05-15');
INSERT INTO questions_pass_record VALUES(105, 'sql', 'pc', 550, '2022-07-25');
INSERT INTO questions_pass_record VALUES(105, 'sql', 'pc', 299, '2020-05-16');
INSERT INTO questions_pass_record VALUES(106, 'java', 'pc', 17, '2021-04-15');
INSERT INTO questions_pass_record VALUES(106, 'java', 'pc', 20, '2021-04-15');
select
user_id
,date
,lead(date,1,'None') over(partition by user_id order by date) nextdate
from questions_pass_record
order by user_id,date
;
十：技能专项-having子句
【题目1：输出提交次数大于2次的用户ID且倒序排列】
--输入：
drop table if exists `done_questions_record`;
create table `done_questions_record` (
user_id int not null comment '用户id',
question_id int not null comment '题目id',
question_type varchar(24) not null comment '题目类型',
done_time datetime not null comment '提交日期',
result_info int not null comment '是否通过，1：通过； 0：不通过'
);
insert into `done_questions_record` values (101, 1, 'python', '2022-01-01 12:30:21', 0);
insert into `done_questions_record` values (101, 1, 'python', '2022-01-01 12:30:22', 1);
insert into `done_questions_record` values (102, 1, 'python', '2022-01-01 14:30:23', 1);
insert into `done_questions_record` values (101, 2, 'sql', '2022-01-01 16:30:24', 1);
insert into `done_questions_record` values (102, 2, 'sql', '2022-01-02 08:30:25', 1);
insert into `done_questions_record` values (103, 1, 'python', '2022-01-03 10:30:26', 0);
insert into `done_questions_record` values (104, 1, 'python', '2022-01-03 11:30:27', 0);
insert into `done_questions_record` values (103, 2, 'sql', '2022-01-03 19:30:28', 1);
insert into `done_questions_record` values (105, 1, 'python', '2022-01-03 12:30:29', 0);
insert into `done_questions_record` values (103, 3, 'java', '2022-01-03 12:30:30', 0);
insert into `done_questions_record` values (105, 3, 'java', '2022-01-03 12:30:31', 1);
insert into `done_questions_record` values (105, 4, 'js', '2022-01-03 12:30:32', 0);
insert into `done_questions_record` values (104, 5, 'c++', '2022-01-03 12:30:33', 1);
insert into `done_questions_record` values (106, 5, 'c++', '2022-01-03 12:30:34', 0);
insert into `done_questions_record` values (101, 5, 'c++', '2022-01-03 12:30:35', 1);
insert into `done_questions_record` values (106, 5, 'c++', '2022-01-03 12:30:36', 1);
insert into `done_questions_record` values (102, 5, 'c++', '2022-01-03 12:30:37', 1);
insert into `done_questions_record` values (103, 4, 'js', '2022-01-03 12:30:38', 1);
insert into `done_questions_record` values (105, 3, 'java', '2022-01-03 12:30:39', 1);
insert into `done_questions_record` values (103, 2, 'sql', '2022-01-03 12:30:40', 0);
insert into `done_questions_record` values (105, 1, 'python', '2022-01-03 12:30:41', 0);
insert into `done_questions_record` values (105, 1, 'python', '2022-01-03 12:30:42', 1);
insert into `done_questions_record` values (104, 2, 'sql', '2022-01-03 12:30:43', 1);
select
user_id
from done_questions_record
group by user_id
having count(1)>2
order by user_id desc
;
【题目2：输出提交且通过次数大于2 的用户ID且升序排列】
--输入：
drop table if exists `done_questions_record`;
create table `done_questions_record` (
user_id int not null comment '用户id',
question_id int not null comment '题目id',
question_type varchar(24) not null comment '题目类型',
done_time datetime not null comment '提交日期',
result_info int not null comment '是否通过，1：通过； 0：不通过'
);
insert into `done_questions_record` values (101, 1, 'python', '2022-01-01 12:30:21', 0);
insert into `done_questions_record` values (101, 1, 'python', '2022-01-01 12:30:22', 1);
insert into `done_questions_record` values (102, 1, 'python', '2022-01-01 14:30:23', 1);
insert into `done_questions_record` values (101, 2, 'sql', '2022-01-01 16:30:24', 1);
insert into `done_questions_record` values (102, 2, 'sql', '2022-01-02 08:30:25', 1);
insert into `done_questions_record` values (103, 1, 'python', '2022-01-03 10:30:26', 0);
insert into `done_questions_record` values (104, 1, 'python', '2022-01-03 11:30:27', 0);
insert into `done_questions_record` values (103, 2, 'sql', '2022-01-03 19:30:28', 1);
insert into `done_questions_record` values (105, 1, 'python', '2022-01-03 12:30:29', 0);
insert into `done_questions_record` values (103, 3, 'java', '2022-01-03 12:30:30', 0);
insert into `done_questions_record` values (105, 3, 'java', '2022-01-03 12:30:31', 1);
insert into `done_questions_record` values (105, 4, 'js', '2022-01-03 12:30:32', 0);
insert into `done_questions_record` values (104, 5, 'c++', '2022-01-03 12:30:33', 1);
insert into `done_questions_record` values (106, 5, 'c++', '2022-01-03 12:30:34', 0);
insert into `done_questions_record` values (101, 5, 'c++', '2022-01-03 12:30:35', 1);
insert into `done_questions_record` values (106, 5, 'c++', '2022-01-03 12:30:36', 1);
insert into `done_questions_record` values (102, 5, 'c++', '2022-01-03 12:30:37', 1);
insert into `done_questions_record` values (103, 4, 'js', '2022-01-03 12:30:38', 1);
insert into `done_questions_record` values (105, 3, 'java', '2022-01-03 12:30:39', 1);
insert into `done_questions_record` values (103, 2, 'sql', '2022-01-03 12:30:40', 0);
insert into `done_questions_record` values (105, 1, 'python', '2022-01-03 12:30:41', 0);
insert into `done_questions_record` values (105, 1, 'python', '2022-01-03 12:30:42', 1);
insert into `done_questions_record` values (104, 2, 'sql', '2022-01-03 12:30:43', 1);
select
user_id
from done_questions_record
where result_info='1'
group by user_id
having count(1)>2
;
【题目3**：验证刷题效果，输出题目真实通过率】
--输入：
drop table if exists `done_questions_record`;
create table `done_questions_record` (
user_id int not null comment '用户id',
question_id int not null comment '题目id',
question_type varchar(24) not null comment '题目类型',
done_time datetime not null comment '提交日期',
result_info int not null comment '是否通过，1：通过； 0：不通过'
);
insert into `done_questions_record` values (101, 1, 'python', '2022-01-01 12:30:21', 0);
insert into `done_questions_record` values (101, 1, 'python', '2022-01-01 12:30:22', 1);
insert into `done_questions_record` values (102, 1, 'python', '2022-01-01 14:30:23', 1);
insert into `done_questions_record` values (101, 2, 'sql', '2022-01-01 16:30:24', 1);
insert into `done_questions_record` values (102, 2, 'sql', '2022-01-02 08:30:25', 1);
insert into `done_questions_record` values (103, 1, 'python', '2022-01-03 10:30:26', 0);
insert into `done_questions_record` values (104, 1, 'python', '2022-01-03 11:30:27', 0);
insert into `done_questions_record` values (103, 2, 'sql', '2022-01-03 19:30:28', 1);
insert into `done_questions_record` values (105, 1, 'python', '2022-01-03 12:30:29', 0);
insert into `done_questions_record` values (103, 3, 'java', '2022-01-03 12:30:30', 0);
insert into `done_questions_record` values (105, 3, 'java', '2022-01-03 12:30:31', 1);
insert into `done_questions_record` values (105, 4, 'js', '2022-01-03 12:30:32', 0);
insert into `done_questions_record` values (104, 5, 'c++', '2022-01-03 12:30:33', 1);
insert into `done_questions_record` values (106, 5, 'c++', '2022-01-03 12:30:34', 0);
insert into `done_questions_record` values (101, 5, 'c++', '2022-01-03 12:30:35', 1);
insert into `done_questions_record` values (106, 5, 'c++', '2022-01-03 12:30:36', 1);
insert into `done_questions_record` values (102, 5, 'c++', '2022-01-03 12:30:37', 1);
insert into `done_questions_record` values (103, 4, 'js', '2022-01-03 12:30:38', 1);
insert into `done_questions_record` values (105, 3, 'java', '2022-01-03 12:30:39', 1);
insert into `done_questions_record` values (103, 2, 'sql', '2022-01-03 12:30:40', 0);
insert into `done_questions_record` values (105, 1, 'python', '2022-01-03 12:30:41', 0);
insert into `done_questions_record` values (105, 1, 'python', '2022-01-03 12:30:42', 1);
insert into `done_questions_record` values (104, 2, 'sql', '2022-01-03 12:30:43', 1);
question_pass_rate：题目通过率=通过题目总数(去重)/总题目数(去重)
pass_rate：提交通过正确率=通过题目总数(不去重)/提交次数(不去重)
question_per_cnt：每题目平均提交次数=总提交次数(不去重)/总题目数(去重)
select
user_id
,count(distinct if(result_info=1,question_id,null))/count(distinct question_id) question_pass_rate
,sum(result_info)/count(done_time) pass_rate
,count(done_time)/count(distinct question_id) question_per_cnt
from done_questions_record
group by user_id
having question_pass_rate>0.6
order by user_id
;
十一：技能专项-一网打尽时间函数
【题目1**：广告点击的高峰期】
--输入：
drop table if exists user_ad_click_time;
create table `user_ad_click_time` (
user_id bigint comment '用户id',
trace_id varchar(128) comment '订单点击支付追踪id,唯一不重复',
click_time datetime comment '用户点击广告时间'
);
drop table if exists user_payment_time;
create table `user_payment_time` (
user_id bigint comment '用户id',
trace_id varchar(128) comment '订单点击支付追踪id，唯一不重复',
pay_time datetime comment '用户支付订单时间'
);
insert into `user_ad_click_time` values (749253204,'eff7ffbd-deac-4af0-79fe-fff0dbdaf3ac','2022-01-01 12:30:21');
insert into `user_ad_click_time` values (931602698,'34dc44ba-14e7-4f6f-80f1-57ef6cfaea8f','2022-01-01 12:30:22');
insert into `user_ad_click_time` values (766516976,'1aea22c9d170ca4b','2022-01-01 14:30:23');
insert into `user_ad_click_time` values (627853308,'df9f6951-dcff-c054-feca-d66ddbfd9f46','2022-01-01 16:30:24');
insert into `user_ad_click_time` values (140298186,'ffbfe7f9-e7ff-697c-fb75-bbfdfeffea29','2022-01-02 08:30:25');
insert into `user_ad_click_time` values (681978403,'20eaa429-b1eb-4326-bd1e-58cdacdda942','2022-01-03 10:30:26');
insert into `user_ad_click_time` values (650539134,'dbf74a3c-f8f7-4798-f5d3-3c69ffe71e53','2022-01-03 11:30:27');
insert into `user_ad_click_time` values (435496502,'f357ad3f-b41f-a20d-7ddb-dff3eb7fec48','2022-01-03 19:30:28');
insert into `user_ad_click_time` values (243248339,'d91b7346-6e38-45dc-8f48-9b061f905427','2022-01-03 12:30:29');
insert into `user_ad_click_time` values (443379063,'34eb9bae-5deb-44df-a51a-716f413d25bd','2022-01-03 12:30:30');
insert into `user_ad_click_time` values (861582803,'a67d6927-6f62-4f67-86cd-f649a8c65ffd','2022-01-03 12:30:31');
insert into `user_ad_click_time` values (729889099,'b8ac6503-7e2b-4e24-90ed-3825a577c986','2022-01-03 12:30:32');
insert into `user_ad_click_time` values (67508429,'bee9dcd7-bfe6-338f-c7f6-5febbddb5922','2022-01-03 12:30:33');
insert into `user_ad_click_time` values (577814499,'92aa2394-6abe-4a02-b329-1d76c2762fe7','2022-01-03 12:30:34');
insert into `user_ad_click_time` values (92572386,'0f62d5d5-876f-4559-95b3-0d221e4e1811','2022-01-03 12:30:35');
insert into `user_ad_click_time` values (628619549,'df5fbcb5-d7f5-2a8e-d3bf-fbfffebfaafb','2022-01-03 12:30:36');
insert into `user_payment_time` values (931602698,'34dc44ba-14e7-4f6f-80f1-57ef6cfaea8f','2022-01-01 13:30:22');
insert into `user_payment_time` values (766516976,'1aea22c9d170ca4b','2022-01-01 15:30:23');
insert into `user_payment_time` values (627853308,'df9f6951-dcff-c054-feca-d66ddbfd9f46','2022-01-01 16:39:24');
insert into `user_payment_time` values (140298186,'ffbfe7f9-e7ff-697c-fb75-bbfdfeffea29','2022-01-02 08:38:25');
insert into `user_payment_time` values (681978403,'20eaa429-b1eb-4326-bd1e-58cdacdda942','2022-01-03 10:30:26');
insert into `user_payment_time` values (650539134,'dbf74a3c-f8f7-4798-f5d3-3c69ffe71e53','2022-01-05 11:30:27');
insert into `user_payment_time` values (435496502,'f357ad3f-b41f-a20d-7ddb-dff3eb7fec48','2022-01-03 21:30:28');
insert into `user_payment_time` values (628619549,'df5fbcb5-d7f5-2a8e-d3bf-fbfffebfaafb','2022-01-03 15:34:45');
insert into `user_payment_time` values (237634757,'aa382d08-82af-4fea-be36-24b6fd887f98','2022-01-05 12:30:37');
insert into `user_payment_time` values (136197881,'870cacbbc3d9f4ba','2022-01-02 12:30:38');
insert into `user_payment_time` values (358267111,'1e6b0d15-d2ec-47bb-8c73-ba1ee064051e','2022-01-06 12:30:39');
insert into `user_payment_time` values (231101843,'b854003a5a6ddd59','2022-01-04 16:30:40');
insert into `user_payment_time` values (749253204,'eff7ffbd-deac-4af0-79fe-fff0dbdaf3ac1','2022-01-03 12:32:22');
insert into `user_payment_time` values (766516976,'1aea22c9d170ca4b1','2022-01-03 12:31:40');
insert into `user_payment_time` values (650539134,'dbf74a3c-f8f7-4798-f5d3-3c69ffe71e531','2022-01-03 12:33:30');
insert into `user_payment_time` values (443379063,'34eb9bae-5deb-44df-a51a-716f413d25bd1','2022-01-03 12:34:49');
insert into `user_payment_time` values (237634757,'aa382d08-82af-4fea-be36-24b6fd887f981','2022-01-03 12:35:42');
select
click_hour
,click_cnt
from(
select
hour(click_time) click_hour
,count(trace_id) click_cnt
,row_number() over(order by count(trace_id) desc) rn
from user_ad_click_time
group by hour(click_time)
) a
where rn=1
;
【题目2**：输出在5min内完成点击购买的用户ID】
--输入：
drop table if exists user_ad_click_time;
create table `user_ad_click_time` (
user_id bigint comment '用户id',
trace_id varchar(128) comment '订单点击支付追踪id,唯一不重复',
click_time datetime comment '用户点击广告时间'
);
drop table if exists user_payment_time;
create table `user_payment_time` (
user_id bigint comment '用户id',
trace_id varchar(128) comment '订单点击支付追踪id，唯一不重复',
pay_time datetime comment '用户支付订单时间'
);
insert into `user_ad_click_time` values (749253204,'eff7ffbd-deac-4af0-79fe-fff0dbdaf3ac','2022-01-01 12:30:21');
insert into `user_ad_click_time` values (931602698,'34dc44ba-14e7-4f6f-80f1-57ef6cfaea8f','2022-01-01 12:30:22');
insert into `user_ad_click_time` values (766516976,'1aea22c9d170ca4b','2022-01-01 14:30:23');
insert into `user_ad_click_time` values (627853308,'df9f6951-dcff-c054-feca-d66ddbfd9f46','2022-01-01 16:30:24');
insert into `user_ad_click_time` values (140298186,'ffbfe7f9-e7ff-697c-fb75-bbfdfeffea29','2022-01-02 08:30:25');
insert into `user_ad_click_time` values (681978403,'20eaa429-b1eb-4326-bd1e-58cdacdda942','2022-01-03 10:30:26');
insert into `user_ad_click_time` values (650539134,'dbf74a3c-f8f7-4798-f5d3-3c69ffe71e53','2022-01-03 11:30:27');
insert into `user_ad_click_time` values (435496502,'f357ad3f-b41f-a20d-7ddb-dff3eb7fec48','2022-01-03 19:30:28');
insert into `user_ad_click_time` values (243248339,'d91b7346-6e38-45dc-8f48-9b061f905427','2022-01-03 12:30:29');
insert into `user_ad_click_time` values (443379063,'34eb9bae-5deb-44df-a51a-716f413d25bd','2022-01-03 12:30:30');
insert into `user_ad_click_time` values (861582803,'a67d6927-6f62-4f67-86cd-f649a8c65ffd','2022-01-03 12:30:31');
insert into `user_ad_click_time` values (729889099,'b8ac6503-7e2b-4e24-90ed-3825a577c986','2022-01-03 12:30:32');
insert into `user_ad_click_time` values (67508429,'bee9dcd7-bfe6-338f-c7f6-5febbddb5922','2022-01-03 12:30:33');
insert into `user_ad_click_time` values (577814499,'92aa2394-6abe-4a02-b329-1d76c2762fe7','2022-01-03 12:30:34');
insert into `user_ad_click_time` values (92572386,'0f62d5d5-876f-4559-95b3-0d221e4e1811','2022-01-03 12:30:35');
insert into `user_ad_click_time` values (628619549,'df5fbcb5-d7f5-2a8e-d3bf-fbfffebfaafb','2022-01-03 12:30:36');
insert into `user_payment_time` values (931602698,'34dc44ba-14e7-4f6f-80f1-57ef6cfaea8f','2022-01-01 13:30:22');
insert into `user_payment_time` values (766516976,'1aea22c9d170ca4b','2022-01-01 15:30:23');
insert into `user_payment_time` values (627853308,'df9f6951-dcff-c054-feca-d66ddbfd9f46','2022-01-01 16:39:24');
insert into `user_payment_time` values (140298186,'ffbfe7f9-e7ff-697c-fb75-bbfdfeffea29','2022-01-02 08:38:25');
insert into `user_payment_time` values (681978403,'20eaa429-b1eb-4326-bd1e-58cdacdda942','2022-01-03 10:30:26');
insert into `user_payment_time` values (650539134,'dbf74a3c-f8f7-4798-f5d3-3c69ffe71e53','2022-01-05 11:30:27');
insert into `user_payment_time` values (435496502,'f357ad3f-b41f-a20d-7ddb-dff3eb7fec48','2022-01-03 21:30:28');
insert into `user_payment_time` values (628619549,'df5fbcb5-d7f5-2a8e-d3bf-fbfffebfaafb','2022-01-03 15:34:45');
insert into `user_payment_time` values (237634757,'aa382d08-82af-4fea-be36-24b6fd887f98','2022-01-05 12:30:37');
insert into `user_payment_time` values (136197881,'870cacbbc3d9f4ba','2022-01-02 12:30:38');
insert into `user_payment_time` values (358267111,'1e6b0d15-d2ec-47bb-8c73-ba1ee064051e','2022-01-06 12:30:39');
insert into `user_payment_time` values (231101843,'b854003a5a6ddd59','2022-01-04 16:30:40');
insert into `user_payment_time` values (749253204,'eff7ffbd-deac-4af0-79fe-fff0dbdaf3ac1','2022-01-03 12:32:22');
insert into `user_payment_time` values (766516976,'1aea22c9d170ca4b1','2022-01-03 12:31:40');
insert into `user_payment_time` values (650539134,'dbf74a3c-f8f7-4798-f5d3-3c69ffe71e531','2022-01-03 12:33:30');
insert into `user_payment_time` values (443379063,'34eb9bae-5deb-44df-a51a-716f413d25bd1','2022-01-03 12:34:49');
insert into `user_payment_time` values (237634757,'aa382d08-82af-4fea-be36-24b6fd887f981','2022-01-03 12:35:42');
select
a.user_id uid
from user_ad_click_time a
left join user_payment_time b
on a.user_id=b.user_id and a.trace_id=b.trace_id
where timestampdiff(minute,click_time,pay_time)<=5
order by a.user_id desc
;
十二：技能专项-一网打尽字符函数
【题目1：字符函数正则匹配1（答案有问题）】
--输入：
drop table if exists comment_detail;
create table `comment_detail`(
id int(11) comment 'ID',
comment text comment '评论内容',
subject_set text comment '所属话题, 第一所属，第二所属, ....... 逗号分隔',
type_id int comment '被标记类型'
);
insert into `comment_detail` values (1, '试一 下吧', '1001,1002,1003', 100);
insert into `comment_detail` values (2, '是吗？', '10086,1002,10010', 101);
insert into `comment_detail` values (3, '是的，真的啊！', '', 102);
insert into `comment_detail` values (4, 'hello world', '11002,1002', 100);
insert into `comment_detail` values (5, '你好，中国！', '11002,11024', 104);
insert into `comment_detail` values (6, '天了噜，我表示不敢相信', '', null);
insert into `comment_detail` values (7, '第一人2/3错误率, 第二人一定是1/2错误率, 相乘得1/3', '', 101);
insert into `comment_detail` values (8, '看不懂。。。', '11022,1002', 102);
insert into `comment_detail` values (9, '第2个括号里写错了，应该是(770 - (512-1))', '11022,1002', null);
insert into `comment_detail` values (10, '大家猜猜我是谁', '142197,1002,143834', 104);
insert into `comment_detail` values (11, '假设有 长度为2 那么 m= 2 ,f = 0 ,r = 1 那么计算结果应该是 2+1-0 mod 2 = 1', '11075', 100);
insert into `comment_detail` values (12, '可以在线oj，( ^_^ )不错嘛', '11003', null);
insert into `comment_detail` values (13, '年轻人可千万别努力错了方向啊~', '11002', null);
insert into `comment_detail` values (14, '老师教过不会就选C，所以 应该是选择排序！', '', null);
insert into `comment_detail` values (15, 'xxmmmxmx', '', null);
insert into `comment_detail` values (16, 's*c/(a+b) ..', '11081', 100);
insert into `comment_detail` values (17, '优点：该方法易于理解，缺点：实现过程太复杂了', '11002,1002,11004', 101);
insert into `comment_detail` values (18, '求送书', '11002', 102);
insert into `comment_detail` values (19, '我来看看', '11234,11007,11053', 100);
insert into `comment_detail` values (20, '这个题目好难，求解答：）', '11022,11023', 101);
insert into `comment_detail` values (21, '楼主好人～', '11022', 103);
insert into `comment_detail` values (22, '我要报名 不过我名字的颜色是？', '', null);
# 能过的答案
select
id
,comment
from comment_detail
where comment like '%是%' or (comment like '%试%')
order by id
;
# 根据题意解
select
id
,comment
from comment_detail
where comment like '是%' or comment like '求%'
order by id
;
【题目2：字符函数正则匹配2】
--输入：
drop table if exists comment_detail;
create table `comment_detail`(
id int(11) comment 'ID',
comment text comment '评论内容',
subject_set text comment '所属话题, 第一所属，第二所属, ....... 逗号分隔',
type_id int comment '被标记类型'
);
insert into `comment_detail` values (1, '试一 下吧', '1001,1002,1003', 100);
insert into `comment_detail` values (2, '是吗？', '10086,1002,10010', 101);
insert into `comment_detail` values (3, '是的，真的啊！', '', 102);
insert into `comment_detail` values (4, 'hello world', '11002,1002', 100);
insert into `comment_detail` values (5, '你好，中国！', '11002,11024', 104);
insert into `comment_detail` values (6, '天了噜，我表示不敢相信', '', null);
insert into `comment_detail` values (7, '第一人2/3错误率, 第二人一定是1/2错误率, 相乘得1/3', '', 101);
insert into `comment_detail` values (8, '看不懂。。。', '11022,1002', 102);
insert into `comment_detail` values (9, '第2个括号里写错了，应该是(770 - (512-1))', '11022,1002', null);
insert into `comment_detail` values (10, '大家猜猜我是谁', '142197,1002,143834', 104);
insert into `comment_detail` values (11, '假设有 长度为2 那么 m= 2 ,f = 0 ,r = 1 那么计算结果应该是 2+1-0 mod 2 = 1', '11075', 100);
insert into `comment_detail` values (12, '可以在线oj，( ^_^ )不错嘛', '11003', null);
insert into `comment_detail` values (13, '年轻人可千万别努力错了方向啊~', '11002', null);
insert into `comment_detail` values (14, '老师教过不会就选C，所以 应该是选择排序！', '', null);
insert into `comment_detail` values (15, 'xxmmmxmx', '', null);
insert into `comment_detail` values (16, 's*c/(a+b) ..', '11081', 100);
insert into `comment_detail` values (17, '优点：该方法易于理解，缺点：实现过程太复杂了', '11002,1002,11004', 101);
insert into `comment_detail` values (18, '求送书', '11002', 102);
insert into `comment_detail` values (19, '我来看看', '11234,11007,11053', 100);
insert into `comment_detail` values (20, '这个题目好难，求解答：）', '11022,11023', 101);
insert into `comment_detail` values (21, '楼主好人～', '11022', 103);
insert into `comment_detail` values (22, '我要报名 不过我名字的颜色是？', '', null);
select
id
,comment
from comment_detail
where comment like '是%' or comment like '求%'
;
【题目3：话题的分布情况】
--输入：
drop table if exists comment_detail;
create table `comment_detail`(
id int(11) comment 'ID',
comment text comment '评论内容',
subject_set text comment '所属话题, 第一所属，第二所属, ....... 逗号分隔',
type_id int comment '被标记类型'
);
insert into `comment_detail` values (1, '试一 下吧', '1001,1002,1003', 100);
insert into `comment_detail` values (2, '是吗？', '10086,1002,10010', 101);
insert into `comment_detail` values (3, '是的，真的啊！', '', 102);
insert into `comment_detail` values (4, 'hello world', '11002,1002', 100);
insert into `comment_detail` values (5, '你好，中国！', '11002,11024', 104);
insert into `comment_detail` values (6, '天了噜，我表示不敢相信', '', null);
insert into `comment_detail` values (7, '第一人2/3错误率, 第二人一定是1/2错误率, 相乘得1/3', '', 101);
insert into `comment_detail` values (8, '看不懂。。。', '11022,1002', 102);
insert into `comment_detail` values (9, '第2个括号里写错了，应该是(770 - (512-1))', '11022,1002', null);
insert into `comment_detail` values (10, '大家猜猜我是谁', '142197,1002,143834', 104);
insert into `comment_detail` values (11, '假设有 长度为2 那么 m= 2 ,f = 0 ,r = 1 那么计算结果应该是 2+1-0 mod 2 = 1', '11075', 100);
insert into `comment_detail` values (12, '可以在线oj，( ^_^ )不错嘛', '11003', null);
insert into `comment_detail` values (13, '年轻人可千万别努力错了方向啊~', '11002', null);
insert into `comment_detail` values (14, '老师教过不会就选C，所以 应该是选择排序！', '', null);
insert into `comment_detail` values (15, 'xxmmmxmx', '', null);
insert into `comment_detail` values (16, 's*c/(a+b) ..', '11081', 100);
insert into `comment_detail` values (17, '优点：该方法易于理解，缺点：实现过程太复杂了', '11002,1002,11004', 101);
insert into `comment_detail` values (18, '求送书', '11002', 102);
insert into `comment_detail` values (19, '我来看看', '11234,11007,11053', 100);
insert into `comment_detail` values (20, '这个题目好难，求解答：）', '11022,11023', 101);
insert into `comment_detail` values (21, '楼主好人～', '11022', 103);
insert into `comment_detail` values (22, '我要报名 不过我名字的颜色是？', '', null);
select
substring_index(subject_set,',',1) subject_id1
,count(1) cnt
from comment_detail
where substring_index(substring_index(subject_set,',',2),',',-1)='1002'
group by substring_index(subject_set,',',1)
order by subject_id1
;
【题目4**：评论替换和去除】
--输入：
drop table if exists comment_detail;
create table `comment_detail`(
id int(11) comment 'ID',
comment text comment '评论内容',
subject_set text comment '所属话题, 第一所属，第二所属, ....... 逗号分隔',
type_id int comment '被标记类型'
);
insert into `comment_detail` values (1, '试一 下吧', '1001,1002,1003', 100);
insert into `comment_detail` values (2, '是吗？', '10086,1002,10010', 101);
insert into `comment_detail` values (3, '是的，真的啊！', '', 102);
insert into `comment_detail` values (4, 'hello world', '11002,1002', 100);
insert into `comment_detail` values (5, '你好，中国！', '11002,11024', 104);
insert into `comment_detail` values (6, '天了噜，我表示不敢相信', '', null);
insert into `comment_detail` values (7, '第一人2/3错误率, 第二人一定是1/2错误率, 相乘得1/3', '', 101);
insert into `comment_detail` values (8, '看不懂。。。', '11022,1002', 102);
insert into `comment_detail` values (9, '第2个括号里写错了，应该是(770 - (512-1))', '11022,1002', null);
insert into `comment_detail` values (10, '大家猜猜我是谁', '142197,1002,143834', 104);
insert into `comment_detail` values (11, '假设有 长度为2 那么 m= 2 ,f = 0 ,r = 1 那么计算结果应该是 2+1-0 mod 2 = 1', '11075', 100);
insert into `comment_detail` values (12, '可以在线oj，( ^_^ )不错嘛', '11003', null);
insert into `comment_detail` values (13, '年轻人可千万别努力错了方向啊~', '11002', null);
insert into `comment_detail` values (14, '老师教过不会就选C，所以 应该是选择排序！', '', null);
insert into `comment_detail` values (15, 'xxmmmxmx', '', null);
insert into `comment_detail` values (16, 's*c/(a+b) ..', '11081', 100);
insert into `comment_detail` values (17, '优点：该方法易于理解，缺点：实现过程太复杂了', '11002,1002,11004', 101);
insert into `comment_detail` values (18, '求送书', '11002', 102);
insert into `comment_detail` values (19, '我来看看', '11234,11007,11053', 100);
insert into `comment_detail` values (20, '这个题目好难，求解答：）', '11022,11023', 101);
insert into `comment_detail` values (21, '楼主好人～', '11022', 103);
insert into `comment_detail` values (22, '我要报名 不过我名字的颜色是？', '', null);
select
id
,replace(comment,'，','') comment
from comment_detail
where length(replace(comment,'，',''))>3
由于题目说的是汉字长度大于3，所以这里就要使用char_length()而不是length();
char_length()：单位为字符，不管汉字还是数字或者是字母都算是一个字符。
length()： 单位是字节，utf8编码下,一个汉字三个字节，一个数字或字母一个字节。gbk编码下,一个汉字两个字节，一个数字或字母一个字节。
520
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
