牛客网SQL训练2—SQL基础进阶

---

一、基本查询

【题目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编码下,一个汉字两个字节，一个数字或字母一个字节。