文章目录
- 一、基本查询
- 二、数据过滤
- 三：函数
- 四：分组聚合
- 五：子查询
- 六：多表连接
- 七：组合查询
- 八：技能专项-case when使用
- 九：多表连接-窗口函数
- 十：技能专项-having子句
- 十一：技能专项-一网打尽时间函数
- 十二：技能专项-一网打尽字符函数
一、基本查询
【题目1：查询所有投递用户user id并去重】
select
user_id
from deliver_record
group by user_id
;
【题目2：查询限制行数】
select
user_id
,job_id
,device
,job_salary
,deliver_date
from deliver_record
limit 2
;
【题目3：将查询列重新命名】
select
job_salary '职位工资'
from deliver_record
;
【题目4：查询表总行数】
select
count(*) cnt
from deliver_record
;
二、数据过滤
【题目1：查询在pc上投递的所有投递记录】
select * from deliver_record_detail
where device='pc'
;
【题目2：查询投递最低最高薪资差别大于2的职位的投递用户】
select
user_id
from deliver_record_detail
where (max_salary-min_salary)>2
;
【题目3**：查询薪资信息不为空的职位投递记录】
select * from deliver_record_detail
where min_salary is not null or max_salary is not null
;
【题目4：查询城市为北京的职位投递记录】
select * from deliver_record_detail
where job_city like '北京%'
;
三：函数
【题目1：计算总刷题数,并将所选列名改为总刷题数】
select
sum(pass_count) '总刷题数'
from questions_pass_record_detail
;
【题目2：计算刷题总人数】
select
count(distinct user_id) cnt
from questions_pass_record_detail
;
【题目3：找出sql类题目的单次最大刷题数】
select
max(pass_count) maxCnt
from questions_pass_record_detail
where question_type='sql'
;
【题目4：计算单次平均刷题数】
select
avg(pass_count) avgCnt
from questions_pass_record_detail
;
四：分组聚合
【题目1：统计每天总刷题数】
select
date days
,sum(pass_count) passCnt
from questions_pass_record_detail
group by date
;
【题目2：统计每天刷题数超过5的user id以及刷题数】
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：统计不同类型题目的刷题数，并按刷题数进行升序排列】
select
question_type
,sum(pass_count) passCnt
from questions_pass_record_detail
group by question_type
order by passCnt
;
五：子查询
【题目1：查询2022年毕业用户的刷题记录】
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和毕业院校】
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：查询被投递过的职位信息（答案有问题）】
# 能过的答案
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：查询每个公司查看过的投递用户数】
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】
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】
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：判断其是否有过购买记录】
select
customer_id
,if(latest_place_order_date is not null,1,0) if_placed_order
from customers_info
;
【题目2：请按城市对客户进行排序,如果城市为空，则按国家排序】
select
customer_id
,gender
,city
,country
,age
,latest_place_order_date
from customers_info
order by city,country
;
【题目3：分群并计算群体人数】
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和刷题数】
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：查询用户刷题日期和下一次刷题日期】
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且倒序排列】
select
user_id
from done_questions_record
group by user_id
having count(1)>2
order by user_id desc
;
【题目2：输出提交且通过次数大于2 的用户ID且升序排列】
select
user_id
from done_questions_record
where result_info='1'
group by user_id
having count(1)>2
;
【题目3**：验证刷题效果，输出题目真实通过率】
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**：广告点击的高峰期】
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】
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（答案有问题）】
# 能过的答案
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】
select
id
,comment
from comment_detail
where comment like '是%' or comment like '求%'
;
【题目3：话题的分布情况】
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**：评论替换和去除】
select
id
,replace(comment,'，','') comment
from comment_detail
where length(replace(comment,'，',''))>3
由于题目说的是汉字长度大于3，所以这里就要使用char_length()而不是length();
char_length()：单位为字符，不管汉字还是数字或者是字母都算是一个字符。
length()： 单位是字节，utf8编码下,一个汉字三个字节，一个数字或字母一个字节。gbk编码下,一个汉字两个字节，一个数字或字母一个字节。
