牛客网SQL入门39题思路和代码答案

题3查询结果去重：distinct

题4查询结果限制返回行数：LIMIT 子句;  --可选（limit i,n ：限制查询结果返回的数量，i为查询结果的索引值，默认为0，i=0时可以省略，n为查询结果返回的数量）

题6、7，where

题8，where + between and

题9，查找除复旦大学的用户信息， NOT IN ()，或者是！=

题10，where过滤空值，is not null

题11、12、13、14高级操作符练习：and、or、in(value1,value2,value3,...)

题15 查询学校名称中包含北京的，like '%ddd%'

题16 查找GPA最高 max + where

题17 计算男生平均人数以及平均GPA ，count计数，avg聚合函数，round(m,n) n为保留小数点位

题18 分组计算 group by

题19 分组过滤 group by + having 或者where 子查询+group by

题20 分组排序，group by + order by

题21 浙江大学用户题目回答情况 join关联两张表，where + order by 或者用子查询

题22 统计每个学校的用户平均答题数（这道题目重点理解平均答题数的计算，再就是需要结合sql语句的执行顺序来写代码）

select u.university, 
count(q.question_id)/count(distinct(q.device_id)) as avg_answer_cnt 
from user_profile as u
join question_practice_detail as q 
on q.device_id=u.device_id
group by university;

题23 统计每个学校各难度的用户平均刷题数（注意使用inner join)

select u.university,
       q2.difficult_level,
       count(q1.question_id)/count(distinct(q1.device_id)) as avg_answer_cnt
from user_profile as u
inner join question_practice_detail as q1 on q1.device_id=u.device_id
inner join question_detail as q2 on q2.question_id=q1.question_id
group by university, q2.difficult_level;

题24 统计每个用户的平均刷题数（或者去掉where用having加在group by后面也可以），但是就需要在group by中加上 u.university，因为having是在group by后面执行的，不加结果会出错）

select u.university,
       q2.difficult_level,
       count(q1.question_id)/count(distinct(q1.device_id)) as avg_answer_cnt
from user_profile as u
inner join question_practice_detail as q1 on q1.device_id=u.device_id
inner join question_detail as q2 on q2.question_id=q1.question_id
where u.university='山东大学'
group by q2.difficult_level;

题25 查找山东大学或男生的gpa，结果不去重，需要用到union all

select device_id, gender, age, gpa from user_profile
where university='山东大学' 
union all
select device_id, gender, age, gpa from user_profile
where gender='male';

题26 计算25岁以上和以下的用户数量（考查if(a,b,c)函数的使用，判断条件a，满足返回b，不满足则返回c）

select if(age>=25,'25岁及以上','25岁以下') as age_cut,
       count(device_id) as number
from user_profile
group by age_cut;

题27 查看不同年龄段的用户明细（if的多层嵌套，理清逻辑即可）

select device_id, gender,
       if(age>=25,'25岁以上',
         if(age>=20 and age<25,'20-24岁',
           if(age<20,'20岁以下','其他'))) as age_cut
from user_profile
group by device_id;

题 28 计算用户8月每天的练题数量（日期函数的使用）

select extract(day from date) as day,
       count(device_id) as question_cnt
from question_practice_detail
where date>='2021-08-01'
group by day;

题29 计算用户的平均次日留存率

select count(date2)/count(date1) 
from (select distinct q1.device_id, q1.date as date1, q2.date as date2 from question_practice_detail as q1
    left join question_practice_detail as q2 
    on datediff(q2.date,q1.date)=1 and q1.device_id=q2.device_id) as q3

select avg(if(q2.device_id is not null,1,0)) as avg_ret
from (select distinct device_id, date from question_practice_detail) as q1
      left join 
     (select distinct device_id, date_sub(date, interval 1 day) as date2 from question_practice_detail) as q2
      on q1.device_id=q2.device_id and q1.date=q2.date2

题30 统计每种性别的人数，考查字符串截取函数

字符串按索引截取：SUBSTRING_INDEX(原始字符串，分隔符，n) ，获取原始字符串按照分隔符分割后，第n个分隔符之前(或之后)的子字符串，支持正向和反向索引，索引起始值为1(或-1)。

select if(substring_index(profile,',',-1)='male','male','female') as gender,
count(device_id)
from user_submit
group by gender

题31 提取博客URL中的用户名

select device_id, substring_index(blog_url,'/',-1) as user_name
from user_submit;

题32 截取出年龄

--使用substring截取指定长度字符串
select distinct(substring(profile,12,2)) as age,
count(device_id)
from user_submit
group by age

--套娃写法
select distinct(substring_index(substring_index(profile,',',-2),',',1)) as age,
count(device_id) as number
from user_submit
group by age;

题33 找出每个学校GPA最低的同学

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

--窗口函数写法
select device_id, university, gpa from 
(select device_id, university, gpa, 
 row_number() over (partition by university
                    order by gpa) as rk from user_profile) as a
 where rk=1;

题34 统计复旦用户8月练题情况（注意连结方式，题目不够完整，没有月份信息）

select u.device_id, u.university,
       count(q.device_id) as question_cnt,
       sum(if(q.result='right',1,0)) as right_question_cnt
from user_profile as u
left join question_practice_detail as q 
on u.device_id=q.device_id
where u.university='复旦大学' and (extract(month from date) = 8 or extract(month from date) is null)
group by u.device_id;

题35 浙大不同难度题目的正确率 多表连接，正确率的计算公式要想清楚

select q1.difficult_level,
sum(if(q2.result='right',1,0))/count(q2.result) as correct_rate
from question_detail as q1
left join question_practice_detail as q2 on q1.question_id=q2.question_id
left join user_profile as u on q2.device_id=u.device_id
where u.university='浙江大学'
group by q1.difficult_level
order by correct_rate;

题36、37、38 查找后排序，order by多参数，默认是asc升序，列名后 desc表示降序

题39 21年8月份练题总数（有个like写法思路不错，where date like '2021-08%'）

select count(distinct(device_id)) as did_cnt,
       count(question_id) asquestion_cnt
from question_practice_detail
where extract(month from date)=8;