SQL实战每日一题（基础篇完结）

$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+1$条数据开始，取$b$条
limit b offset a表示跳过前$a$条数据，也就是从第$a+1$条数据开始，取$b$条

之后考虑一下，发现最晚入职的员工不一定只有一人，所以需要找到所有在最晚入职时间入职的员工
正解：

select * from employees 
where hire_date = (select max(hire_date) from employees);

---

$2021.11.22$
在写牛客的SQL基础复习

---

$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$
某个学校或某个性别学生的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$
查看不同年龄段的用户明细

本题考查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$
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$
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$
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$
SQL31 提取博客URL中的用户名

根据分隔符提取URL中的用户名

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

$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$
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$
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$
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