牛客网【SQL】刷题

 首先，将题中常用的函数写在前面:

 SQL语句执行顺序：
FROM - ON - JOIN - WHERE - GROUP BY - WITH - HAVING - SELECT - DISTINCT - ORDER BY - LIMIT

字符截取：

1、LOCATE(substr , str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，则返回0；

2、POSITION(substr  IN str )：返回子串 substr 在字符串 str 中第一次出现的位置，如果字符substr在字符串str中不存在，与LOCATE函数作用相同；

3、LEFT(str, length)：从左边开始截取str，length是截取的长度；

4、RIGHT(str, length)：从右边开始截取str，length是截取的长度；

5、SUBSTRING_INDEX(str  ,substr  ,n)：返回字符substr在str中第n次出现位置之前的字符串;

6、SUBSTRING(str  ,n ,m)：返回字符串str从第n个字符，截取m个字符；

7、REPLACE(str, n, m)：将字符串str中的n字符替换成m字符；

8、LENGTH(str)：计算字符串str的长度。

9、concat(str1,str2,...)：将多个字符串拼接成一个字符串

10、concat_ws(separator,str1,str2,...)：一次性指定分隔符，第一个参数指定分隔符 分隔符不能为空 如果为NULL 则返回值NULL

11、group_concat([distinct]要连接的字段 [order by 排序字段 asc/desc][separator'分隔符'])
 说明：通过使用distinct可以排除重复值 如果希望对结果中的值进行排序 可以使用order by子句 separator是一个字符串值。将group by产生的同一个分组中的值连接起来 返回一个字符串结果

#示例
#2021-07-02:SQL;2021-07-05:SQL;2021-09-01:算法;2021-09-02:SQL;2021-09-05:SQL
group_concat(distinct concat_ws(':',date(start_time),tag) order by start_time separator ';')

时间戳-日期格式转化

时间戳和日期格式之间可以利用from_unixtime和 unix_timestamp进行转换。unix_timestamp可以将日期转换回时间戳  from_unixtime(time,'yyyy-MM-dd')

年月日截取

SQL为此提供了对应的年、月、日提取函数，分别为year(),month(),day()。

日期差计算

-datedff

datediff的作用为计算两个日期之间的天数间隔，语法为datediff(date1,date2)，返回

起始时间 date1 和结束时间 date2 之间的天数，date1大于date2的情况下，返回的天

数为正数，date1小于date2的情况下，返回的天数为负数。

语法为：datediff('2022-07–15','2022-07-01')

-date_sub

语法为date_sub (string startdate, interval int day) ，返回开始日期startdate减少days天后的日期。

-date_add

语法为date_add(string startdate, interval int day) ，返回开始日期startdate增加days天后的日期

timestampdiff(unit,begin,end): 返回begin-end的结果(时间差)。

unit可以是microsecond毫秒 second秒 minute分钟 hour小时 day天 week周 month月边shi

---

19 分组过滤题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

/*因为 where 的操作对象是一条记录，比如说当一条记录xxx时，在什么条件下可以用 where
但是涉及到多条记录时，比如说这里的对象是从一组一组中再过滤到"平均问问题书小于 5" 的小组
这种情况下，where 就不适用了
此时，提供了 having 的关键词。
where 从记录中法过滤出某一条记录
having 可以从一组组记录中过滤掉其哪几组*/
select university,
    round(avg(question_cnt),1) avg_question_cnt,
    round(avg(answer_cnt),1) avg_answer_cnt
from user_profile
group by university
having   avg_question_cnt  <5 or   avg_answer_cnt <20

18 分组计算题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

select gender,university,count(gender) as user_num,
    avg(round(active_days_within_30,1)) as avg_active_day,
    avg(ROUND(question_cnt,1)) as avg_question_cnt
from user_profile
group by gender,university;

 20 分组排序题目：现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据。

select university,
    avg(question_cnt) avg_question_cnt
from user_profile
group by university
order by avg_question_cnt ASC

21 题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

select
  u.device_id,
  q.question_id,
  q.result
from
  question_practice_detail q,
  user_profile u
where
  u.university = '浙江大学'
  and q.device_id = u.device_id;
#连接查询
select
    u.device_id,
    q.question_id,
    q.result 
from question_practice_detail q
join user_profile u 
on q.device_id=u.device_id
where university="浙江大学"
#子查询
select device_id,question_id,result
from question_practice_detail
where device_id=(
select device_id 
from user_profile 
where university='浙江大学')
    

22 运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据。

/*
select 查询结果 [university,'每个人平均答题数量':问题数/设备数(一个设备对应多个题，要去重)]
from 从哪张表中查找数据[两张表联结]
where 查询条件 [无]
group by 分组条件 [university]
*/   
select
  university,
  round(count(q.question_id) / count(distinct u.device_id),4) avg_answer_cnt
from
    user_profile u,
      question_practice_detail q
where
      u.device_id = q.device_id
group by
      university;
  

23 题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

select 
    u.university,
    qd.difficult_level,
    round(count(qp.question_id)/count(distinct(qp.device_id)),4) avg_answer_cnt
from user_profile u,question_practice_detail qp,question_detail qd
where 
    u.device_id=qp.device_id 
    and 
    qp.question_id=qd.question_id
group by u.university,qd.difficult_level;

26 题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

#条件函数if。if（x=n,a,b）表示如果x=n,则返回a，否则就是b。
select if(age>=25 , '25岁及以上','25岁以下') age_cut,
    count(device_id) number
from user_profile
group by age_cut;
/*
CASE 测试表达式
	WHEN 简单表达式1 THEN 结果表达式1
	WHEN 简单表达式2 THEN 结果表达式2
	ELSE 结果表达式n+1
END
*/
select (case when age>=25 then '25岁及以上' else '25岁以下' end) as age_cut, 
count(device_id) as number
from user_profile
group by age_cut

27 题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据。（注：若年龄为空请返回其他。）

select device_id,gender,
    case
        when age<20 then '20岁以下'
        when age<25 then '20-24岁'
        when age>=25 then '25岁及以上'
        else '其他'
    end age_cut
from user_profile;

28 题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。

/*年月日的截取：
    SQL为此提供了对应的年、月、日提取函数，分别为year(),month(),day()。*/
select day(date) day,count(question_id) question_cnt
from question_practice_detail
where year(date)=2021 and month(date)=08
group by day;

30 题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

#SUBSTRING_INDEX(str,substr,n)：返回字符substr在str中第n次出现位置之前的字符串;
select substring_index(profile,',',-1) gender,count(device_id) number
from user_submit
group by gender;

31 题目：对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据。

select device_id,SUBSTRING_INDEX(blog_url,'/',-1) user_name
from user_submit;  #字符串函数

33 题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。

select device_id,university,round(gpa,4)
from user_profile
where (university,gpa) in
    (select university,min(gpa) from user_profile group by university)
order by university;

34 题目： 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

#USING 的使用方法

#用左连接把user_profile和question_practice_detail
select u.device_id,
    u.university,
    sum(if(result is not null,1,0)) question_cnt,
    sum(if(result='right',1,0)) right_question_cnt
from user_profile u 
left join question_practice_detail qp
on u.device_id=qp.device_id
where university = '复旦大学' and (month(date)=8 or date is null)
group by u.device_id;
#USING:关联查询的两个表的关联字段的字段名称相同，这种情况下，可以使用using代替on
select u.device_id,
    u.university,
    sum(if(result is not null,1,0)) question_cnt,
    sum(if(result='right',1,0)) right_question_cnt
from user_profile u 
left join question_practice_detail qp
using(device_id)
where university = '复旦大学' and (month(date)=8 or date is null)
group by u.device_id;

88 【问题】请编写 SQL 语句，返回订单数量总和不小于100的所有订单号，最后结果按照订单号升序排序。

#注意语句顺序
select order_num
from OrderItems
group by order_num
having sum(quantity) >=100
order by order_num;

95 【问题】编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和SUM(quantity)检索）。

SELECT prod_name,
       (SELECT SUM(quantity)
        FROM OrderItems
        WHERE OrderItems.prod_id = Products.prod_id) AS quant_sold
FROM Products;
 
SELECT prod_name,
       quant_sold
FROM Products, (SELECT prod_id, SUM(quantity) AS quant_sold
                FROM OrderItems
                GROUP BY prod_id) AS OI
WHERE Products.prod_id = OI.prod_id;
 
SELECT prod_name, SUM(quantity)
FROM Products LEFT JOIN OrderItems
ON(Products.prod_id = OrderItems.prod_id)
GROUP BY prod_name;

104 使用 OUTER JOIN 联结 Products 表和 OrderItems 表，返回产品名称（prod_name）和每一项产品的总订单数（不是订单号），并按产品名称升序排序。

select prod_name,count(order_num) orders
from Products p
left outer join OrderItems oi on p.prod_id=oi.prod_id
group by prod_name
order by prod_name;

100 编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）。

提示：需要计算总和（item_price 乘以 quantity）。按总额对结果进行排序，请使用INNER JOIN 语法。

select cust_name,
    sum(item_price*quantity) total_price
from OrderItems oi,Orders o,Customers c
where oi.order_num=o.order_num 
and o.cust_id=c.cust_id
group by cust_name
having total_price>=1000
order by total_price;

select cust_name,
    sum(item_price*quantity) total_price
from OrderItems oi
inner join Orders o on oi.order_num=o.order_num 
inner join Customers c on o.cust_id=c.cust_id
group by cust_name
having total_price>=1000
order by total_price;

112 插入记录：现在有一套ID为9003的高难度SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info，不管该ID试卷是否存在，都要插入成功，请尝试插入它。   #使用replace关键字直接将其替换

replace into examination_info
(exam_id,tag,difficulty,duration,release_time)
values(9003,'SQL','hard','90','2021-01-01 00:00:00');

116 请删除exam_record表中作答时间小于5分钟整且分数不及格（及格线为60分）的记录；

#timestampdiff(unit,begin,end): 返回begin-end的结果(时间差)。
#unit可以是microsecond毫秒 second秒 minute分钟 hour小时 day天 week周 month月
delete from exam_record
where timestampdiff(minute,start_time,submit_time)<5
and score<60;

121 创建索引 在examination_info表创建以下索引

1、添加列级约束 alter table 表名 modify column 字段名 字段类型 新约束；

2、添加表级约束 ALTER table 表名 add 【constraint 约束名】约束类型（字段名） 【外键的引用references】

122 请删除examination_info表上的唯一索引uniq_idx_exam_id和全文索引full_idx_tag

#唯一索引
ALTER TABLE examination_info
ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);

#全文索引
ALTER TABLE examination_info
ADD FULLTEXT INDEX full_idx_tag(tag);

#普通索引
ALTER TABLE examination_info
ADD INDEX idx_duration(duration);

#删除索引
# 法1
drop index uniq_idx_exam_id on examination_info;
drop index full_idx_tag  on examination_info;
# 法2
alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;

123 SQL类别高难度试卷得分的截断平均值（去掉最大值与最小值）

#（总-大-小）/（数-2）
select ei.tag,
    ei.difficulty,
    round( (sum(er.score)-max(er.score)-min(er.score) )/ (count(er.score)-2),1) clip_avg_score
from examination_info ei,exam_record er
where ei.exam_id=er.exam_id
and ei.tag='SQL'
and ei.difficulty='hard';

125 请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。

#首先找到SQL试卷的平均分，然后找到高于该平均分的最低分
#1.利用窗口函数直接求出avg，作为一列，然后score与avg比较
select min(avg_t.score) min_score_over_avg
from
(select er.score,avg(er.score) over() avg_score
from exam_record er
left join examination_info ei
using(exam_id)
where ei.tag='SQL'
and er.score is not null) avg_t
where avg_t.score>=avg_t.avg_score;
#2.先求出平均分，作为筛选条件
select min(er.score) min_score_over_avg
from exam_record er
left join examination_info ei
using(exam_id) 
where ei.tag='SQL' 
and er.score>=
(select avg(er.score)
from exam_record er
left join examination_info ei
using(exam_id) 
where ei.tag='SQL')

难 126 请计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau。

注意点: 1. 为什么要对两个字段去重     count(distinct submit_time)
count(distinct uid,date_format(submit_time,'%Y%m%d')

加上uid，是为了将不同的用户在同一天做得卷子分开

2. 为什么count后可以跟2个字段

count后面接2个字段，只要2个字段的结果一样，那就判断为重复记录，最后只输出一个结果，所以这里语法是可行的
误区：uid只有一个人，那要是这个人有多个记录，count里面结果不就不一样了？
思路：因此这里count后面的date_format，在格式部分具体到了天（'%d'），这样一个用户一天如果提交了2次试卷，在distinct 日期的时候，只会被算作1天，也就是说，每个用户，每天只会被记录一次（如果累计活跃了3天，那这个用户总共会有3条活跃记录），所以在count时候，uid跟submit_time，每一天都是[1,1]，因此没问题

select DATE_FORMAT(start_time,'%Y%m') month,
    round(count(distinct uid,date_format(start_time,'%Y%m%d'))/count(distinct uid),2) avg_active_days,
    count(distinct uid) mau
from exam_record
where year(start_time)=2021
and submit_time is not null
group by month;

难  127请从中统计出2021年每个月里用户的月总刷题数month_q_cnt 和日均刷题数avg_day_q_cnt（按月份升序排序）以及该年的总体情况

#求每月的天数，先用last_day()求出当月最后一天日期，再用day()取天数
#使用union all
#max的作用？
select date_format(submit_time,'%Y%m') submit_month,
    count(question_id) month_q_cnt,
    round(count(question_id) / max(day(last_day(submit_time))),3) avg_day_q_cnt
from practice_record
where year(submit_time)='2021'
group by submit_month
union all
select '2021汇总',
    count(question_id) month_q_cnt,
    round(count(question_id)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time)='2021'
order by submit_month

难 128 请统计2021年每个未完成试卷作答数大于1的有效用户的数据（有效用户指完成试卷作答数至少为1且未完成数小于5），输出用户ID、未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合，按未完成试卷数量由多到少排序

# 条件1：有效用户，指完成试卷作答数至少为1且未完成数小于5
# 条件2：未完成试卷作答数大于1
# 结合得 1<未完成试卷答题数<5 , 完成试卷作答数 >= 1
# 条件3：考试开始年限为 2021年

# 输出内容：未完成试卷作答数、完成试卷作答数、作答过的试卷tag集合
# 排序：未完成试卷数量由多到少
SELECT uid,
	SUM(IF(submit_time IS NULL ,1 ,0)) incomplete_cnt,
	SUM(IF(submit_time IS NULL ,0 ,1)) complete_cnt,
	GROUP_CONCAT(DISTINCT CONCAT_WS(':',DATE(start_time),tag) ORDER BY start_time SEPARATOR ';') detail
FROM exam_record er
JOIN examination_info ei
ON er.`exam_id`=ei.`exam_id`
WHERE YEAR(start_time)=2021
GROUP BY uid
HAVING complete_cnt>=1 AND incomplete_cnt>1 AND incomplete_cnt<5
ORDER BY incomplete_cnt DESC

题目难以理解 129 请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出

select tag,
    count(start_time) tag_cnt
from exam_record er
join examination_info ei
on er.exam_id=ei.exam_id
where uid in
(select uid
from exam_record er
group by uid,month(start_time)
#月总完成数/月数  
having count(submit_time)/count(distinct date_format(submit_time,'%Y%m'))>=3)
group by tag
order by tag_cnt desc

 难 130 请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序

select er.exam_id,
    count(distinct er.uid) uv, #用户作答人数
    round(avg(score),1) avg_score
from examination_info ei
join exam_record er
on ei.exam_id=er.exam_id
where ei.tag='SQL'
#限制sql发卷当天的作答
and date_format(ei.release_time,'%Y%m%d')=date_format(er.start_time,'%Y%m%d')
and er.uid in (select uid
              from user_info
              where level>5)
group by er.exam_id
order by uv desc,avg_score asc;

132 请统计每个题目和每份试卷被作答的人数和次数，分别按照"试卷"和"题目"的uv & pv降序显示

select exam_id tid,
    count(distinct uid) uv,
    count(exam_id) pv
from exam_record
group by tid
union all
select question_id tid,
    count(distinct uid) uv,
    count(question_id) pv
from practice_record
group by tid

order by  left(tid,1) desc,uv desc,pv desc

 133 请写出一个SQL实现：输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。

#2021年里，所有每次试卷得分都能到85分的人
select uid,
    'activity1' activity
from exam_record er
where year(start_time)='2021'
group by uid
having min(score)>=85
union all
#2021年里，至少有一次用了一半时间就完成高难度试卷且分数大于80的人
select uid,
    'activity2' activity
from exam_record er
left join examination_info ei
using(exam_id)
where year(start_time)='2021' 
and ei.difficulty='hard' 
and score>=80 
#至少有一次用了一半时间就完成，timestampdiff计算时间差
and timestampdiff(second,er.start_time,er.submit_time)<=ei.duration*30
group by uid
order by uid

134 请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。

select uid,
    count(distinct er.exam_id) exam_cnt,
    count(distinct pr.submit_time) question_cnt
from exam_record er
left join practice_record pr
using(uid)
where uid in (select er.uid
             from exam_record er
             join user_info ui 
             using(uid)
             join examination_info ei
             using(exam_id)
             where difficulty='hard'
             and tag='SQL'
             and ui.level=7
             group by er.uid
             having avg(score)>80)
and year(er.submit_time)=2021
and er.score is not null
group by er.uid
order by exam_cnt,question_cnt desc