2021.11.21
2021.11.21
2021.11.21
查找最晚入职员工的所有信息
直观来看,是按照入职时间从大到小排序,然后取第一个即可
select * from employees
order by hire_date desc
limit 1;
select * from employees
order by hire_date desc
limit 0, 1;
select * from employees
order by hire_date desc
limit 1 offset 0;
知识点:
limit n
等同于limit 0, n
limit a, b
表示跳过前
a
a
a条数据,也就是从第
a
+
1
a+1
a+1条数据开始,取
b
b
b条
limit b offset a
表示跳过前
a
a
a条数据,也就是从第
a
+
1
a+1
a+1条数据开始,取
b
b
b条
之后考虑一下,发现最晚入职的员工不一定只有一人,所以需要找到所有在最晚入职时间入职的员工
正解:
select * from employees
where hire_date = (select max(hire_date) from employees);
2021.11.22
2021.11.22
2021.11.22
在写牛客的SQL基础复习
2021.11.23
2021.11.23
2021.11.23
统计某个难度下的某个学校学生的平均刷题数
找到关键点:
1、某个难度
2、某个学校
那么我们就需要对查询出来的表进行难度和学校的分类,记为A
最后统计A类的行数(答题数),A类的不同学生的数量
解法一:使用内连接 inner join
,第一步先找到qpd中和up中都存在的device_id,然后指定为具体院校,该题为山东大学的学生才会连接。之后拿连接好的表再去连接qd,得到题的难度,然后按照难度分类即可。
select
university,
difficult_level,
count(qpd.question_id) / count(distinct qpd.device_id) as avg_anwser_cnt
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id = up.device_id and up.university = "山东大学"
inner join question_detail as qd
on qpd.question_id = qd.question_id
group by qd.difficult_level
解法二:使用左外连接left join
,第一步先找到qpd中的所有的学校,然后拿连接过的表去qd中给每道题定难度,这样我们就获得了qpd表中所有题对应的学校和难度。然后对学校和难度进行分类,最后选择山东大学即可。
select
university,
difficult_level,
count(qpd.question_id) / count(distinct qpd.device_id) as avg_anwser_cnt
from question_practice_detail as qpd
left join user_profile as up
on qpd.device_id = up.device_id
left join question_detail as qd
on qpd.question_id = qd.question_id
group by up.university, qd.difficult_level
having up.university = "山东大学"
注意:如果在左连接中预先指定on up.university = "山东大学"
,但是左连接指定的是qpd中的行是不可删除,必须保留的,所以此时得到的除山东大学以外的学校均为null
,那么最后如果不用having
指定,那么就会导致这些学校称为一类。
错误示范:
select
university,
difficult_level,
count(qpd.question_id) / count(distinct qpd.device_id) as avg_anwser_cnt
from question_practice_detail as qpd
left join user_profile as up
on qpd.device_id = up.device_id and up.university = "山东大学"
left join question_detail as qd
on qpd.question_id = qd.question_id
group by up.university, qd.difficult_level
-- having up.university = "山东大学"
查询结果:
2021.11.23
2021.11.23
2021.11.23
某个学校或某个性别学生的gpa
本题考查的是不去重情况下的解,也就是如果某个学生恰好是A学校的,也恰好是B性别的,那么他就会在表中出现两次。
而我们如果使用
select * from user_profile where university = "A" or gender = "B"
或者
select * from user_profile where university = "A"
union
select * from user_profile where gender = "B"
这两种是默认去重的。
而使用union all
是可以做到不去重的
select * from user_profile where university = "A"
union all
select * from user_profile where gender = "B"
2021.11.23
2021.11.23
2021.11.23
查看不同年龄段的用户明细
本题考查case-when-end的用法,即自定义列的值,根据不同的集合情况显示不同的列值
select
device_id,
gender,
case when age < 20 then "20岁以下"
when age between 20 and 24 then "20-24岁"
when age >= 25 then "25岁及以上"
when age is null then "其他"
end as age_cut
from user_profile
2021.11.24
2021.11.24
2021.11.24
SQL30 统计每种性别的人数
参考题解
本题考虑的是按性别划分男女的人数,但是性别是在profile中给出的,需要自行分割。
MySQL中自带的substring_index函数。
substring_index(str, delim, count)
str
表示字段
delim
表示用于分割的字符
count
字符串中第count
个字符的前部分或后部分的字符串
profile中不同信息是以,
分隔,最后一部分字段为性别的值。
在substring_index
中,count
大于0表示从前往后截取第count
个delim
前部分,count
小于0表示从后往前截取第abs(count)
个delim
的后部分。
select substring_index(profile, ',', -1) as gender,
count(device_id) as number
from user_submit
group by gender;
2021.11.25
2021.11.25
2021.11.25
SQL32 截取出年龄
叠加使用substring_index
select
substring_index(substring_index(profile, ",", -2), ",", 1) as age,
count(*) as number
from user_submit
group by age;
2021.11.26
2021.11.26
2021.11.26
SQL29 计算用户的平均次日留存率
考虑到表上的每天都是刷题的一天,那么枚举表上的每一天,查询其下一天是否存在表上即可,注意考虑的是第一天刷题后,第二天来刷题的概率,所以一个用户的相同第一天和第二天只能出现一次。
即最后选取的是distinct device_id, A.date, B.date
然后计算count(B.date) / count(A.date)
即可
select count(date2) / count(date1) as avg_ret
from (
select
distinct A.device_id,
A.date as date1,
B.date as date2
from question_practice_detail as A
left join (
select distinct device_id, date
from question_practice_detail
) as B
on A.device_id = B.device_id
and date_add(A.date, interval 1 day) = B.date
) as AB_date
2021.11.27
2021.11.27
2021.11.27
SQL31 提取博客URL中的用户名
根据分隔符提取URL中的用户名
select
device_id,
substring_index(blog_url, "/", -1) as blog_url
from user_submit;
2021.11.28
2021.11.28
2021.11.28
SQL33 找出每个学校GPA最低的同学
本题首先需要找到每个学校的最低GPA的表,然后联表查询即可
select device_id, A.university, A.gpa
user_profile as A
inner join
from (select university, min(gpa) as gpa from user_profile group by university) as B
on A.university = B.university and A.gpa = B.gpa
order by A.university
2021.11.29
2021.11.29
2021.11.29
SQL34 统计复旦用户8月练题情况
select
up.device_id,
up.university,
count(question_id) as question_cnt,
sum(
case
when result = "right" then 1
else 0
end
) as right_question_cnt
from user_profile as up
left join question_practice_detail as qpd
on qpd.device_id = up.device_id and month(qpd.date) = 8
where up.university = "复旦大学"
group by up.device_id
2021.11.30
2021.11.30
2021.11.30
SQL35 浙大不同难度题目的正确率
本题主要是找到每个qpd表中的题目状态,根据device_id来连接up表,最后根据question_id来连接qd表。
select qd.difficult_level,
sum(if(qpd.result = "right", 1, 0)) / count(qpd.result) as correct_rate
from question_practice_detail as qpd
inner join user_profile as up
on qpd.device_id = up.device_id
inner join question_detail as qd
on qpd.question_id = qd.question_id
where up.university = "浙江大学"
group by qd.difficult_level
order by correct_rate asc;
2021.12.01
2021.12.01
2021.12.01
SQL39 21年8月份练题总数
# 找到所有在qpd中出现的device_id,和所有出现过的question_id
select
count(distinct device_id) as did_cnt,
count(question_id) as question_cnt
from question_practice_detail
where month(date) = 8 and year(date) = 2021