入门39题思路和代码答案"/>
牛客网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 q1left join question_practice_detail as q2 on datediff(q2.date,q1.date)=1 and q1.device_id=q2.device_id) as q3select avg(if(q2.device_id is not null,1,0)) as avg_ret
from (select distinct device_id, date from question_practice_detail) as q1left join (select distinct device_id, date_sub(date, interval 1 day) as date2 from question_practice_detail) as q2on 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 universityorder by gpa) as rk from user_profile) as awhere 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;
更多推荐
牛客网SQL入门39题思路和代码答案
发布评论