牛客网SQL刷题笔记（MySQL）

此博客集合LeetCode、牛客网常见的题型及其解法，侵删

类型1：查找排名第几的数据

SQL2 查找入职员工时间排名倒数第三的员工所有信息

题目：请你查找employees里入职员工时间排名倒数第三的员工所有信息。
注意：可能会存在同一个日期入职的员工，所以入职员工时间排名倒数第三的员工可能不止一个。
员工employees表简况如下:

select * from employees
where hire_date = (select distinct hire_date 
from employees 
order by hire_date desc limit 2,1)

解法2：子查询+group by，目前大部分情况最好用group by，因为group by比distinct快的多。

select * from employees 
where hire_date = (select hire_date 
from employees 
group by hire_date 
order by hire_date desc limit 2,1)

SQL12 获取每个部门中当前员工薪水最高的相关信息

题目：获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列。

类似的题目：leetcode：查询每个部门工资最多的员工

员工表dept_emp简况如下:

薪水表salaries简况如下:

解法：因为每个部门可能有多个最大的salary，因此在第一层查询中不要包括员工的信息，先求出每个部门最大的salary及其对应的dept_no。

select dept_no,max(salary)
from dept_emp join salaries on dept_emp.emp_no=salaries.emp_no 
where salaries.to_date='9999-01-01' and dept_emp.to_date='9999-01-01' 
group by dept_no

再用查找到的记录来对应emp_no。

select dept_emp.dept_no,dept_emp.emp_no,salary as maxSalary
from dept_emp join salaries on dept_emp.emp_no=salaries.emp_no
where (dept_no,salary) in (select dept_no,max(salary)
from dept_emp join salaries on dept_emp.emp_no=salaries.emp_no 
where salaries.to_date='9999-01-01' and dept_emp.to_date='9999-01-01' 
group by dept_no)
ORDER by dept_no

SQL17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

题目：请你获取薪水第二多的员工的emp_no以及其对应的薪水salary，
若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。
薪水表salaries简况如下：

解法1：子查询+distinct筛选出排名第几的值，然后返回去寻找emp_no。解法和此题一样：SQL2 查找入职员工时间排名倒数第三的员工所有信息

select emp_no, salary 
FROM salaries
where to_date = '9999-01-01' 
and salary = (select distinct salary 
from salaries 
order by salary desc
limit 1,1)

解法2：子查询+group by

select emp_no, salary 
FROM salaries
where to_date = '9999-01-01' 
and salary = (select salary 
from salaries 
group by salary
order BY salary desc
limit 1,1)

SQL18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成
员工表employees简况如下:

薪水表salaries简况如下:

解法：内连接+子查询+max嵌套，先查找表中最大的salary，再找小于这个salary的最大的值，如果题目改为查排名第三那就再嵌套一层。

select salaries.emp_no
,max(salary)
,employees.last_name
,employees.first_name
from employees join salaries 
on employees.emp_no=salaries.emp_no
where salary<(select max(salary) from salaries)

leecode176 第二高的薪水

题目：编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null。
题目链接，点此进入

这里用判断空值的函数（ifnull）函数来处理特殊情况。
ifnull(a,b)函数解释：
如果value1不是空，结果返回a
如果value1是空，结果返回b

分析：因为此题结果只需要salary的值，所以不需要子查询，直接输出salary即可，另外，利用ifnull来判断是否存在第二高的薪水。
解法一：distinct

select ifNull((
select distinct Salary
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary

解法一：group by

select ifNull((select salary
from employee
group by salary 
order by salary desc
limit 1,1),Null) as SecondHighestSalary

leecode177 第N高的薪水

题目：编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null。
题目链接，点此进入

解法：排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set N=N-1;
  RETURN (
      # Write your MySQL query statement below.
      select ifNull((select salary
      from employee 
      group by salary 
      order by salary desc 
      limit N,1),Null) as getNthHighestSalary
  );
END

SQL90 获得积分最多的人（二）

题目：请你写一个SQL查找积分增加最高的用户的id(可能有多个)，名字，以及他的总积分是多少，查询结果按照id升序排序，以上例子查询结果如下:
题目链接，点此进入

我的一个错误写法：想法是先利用子查询查出最大的grade_num，然后连接grade_info表和user表，最后根据grade_name来查找出id和name，思路问题不大，写法有问题。
此处最大的问题就是where的问题，where后面的grade_num会被认为是grade_info表和user表中自带的grade_num，而不是sum(grade_num)。

select id,name, sum(grade_num) as grade_num from
grade_info join user on grade_info.user_id=user.id
where grade_num =(select a from
(select id, name, sum(grade_num) as a
from grade_info
join user on grade_info.user_id=user.id
group by id,name
order by a desc
limit 1) t2)
group by id,name

正确写法：先把目标表列好，然后一个where进行筛选即可

select id, name, grade_name from
(select id,name, sum(grade_num) as grade_name from
grade_info join user on grade_info.user_id=user.id
group by id,name) t1
where grade_name =(select a from
(select id, name, sum(grade_num) as a
from grade_info
join user on grade_info.user_id=user.id
group by id,name
order by a desc
limit 1) t2)

解法二：利用with，用T作为临时表，然后用T和user连接，而不是用grade_info和user连接。

with T as
(select user_id, sum(grade_num) as grade_num
from grade_info
group by user_id)

select id, name, grade_num
from T
join user on T.user_id=user.id
where grade_num=(select max(grade_num) from T)

SQL91 获得积分最多的人（三）

题目链接，点此进入
此题相比上题而言，多了一个add和reduce来让用户的积分有增有减。

解法：和上题一样，利用with简化写法，用case when来量化add和reduce

with T as
(select user_id, sum(a) as grade_num from
(select user_id
 , case when type='add' then grade_num*1
        else grade_num*(-1) end as a
from grade_info) t
group by user_id)
 
select id, name, grade_num
from T
join user on T.user_id=user.id
where grade_num=(select max(grade_num) from T)
order by id

类型2：三表连接

SQL19 查找所有员工的last_name和first_name以及对应的dept_name

题目：请你查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工。
员工表employees简况如下:

部门表departments表简况如下:

部门员工关系表dept_emp简况如下:

解法：题目中提到要包括暂时没有分配部门的员工，因此运用两次left join连接嵌套。我们先看实例中第一行的顺序分别为last_name、first_name和dept_name，然后先找last_name、first_name所在的表为employees，再看dept_name在表departments中，但是表employees和表departments没有连接字段，因此两表中间要用dept_emp作为桥梁。表连接的顺序为employees-dept_emp-departments。

1、第一次left
join连接employees表与dept_emp表，得到所有员工的last_name和first_name以及对应的dept_no，也包括暂时没有分配部门的员工

2、第二次left
join连接上表与departments表，即连接dept_no与dept_name，得到所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

select employees.last_name, employees.first_name, dept_name 
from employees 
left join dept_emp 
on employees.emp_no=dept_emp.emp_no
left join departments 
on dept_emp.dept_no=departments.dept_no

结果：

SQL21 查找在职员工自入职以来的薪水涨幅情况

请你查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序。
题目链接，点此进入
员工表employees简况如下:

薪水表salaries简况如下:

解法一：把salaries作为两个表（h和t），构成了三表连接（employees，h，t），然后直接利用join来连接。

select employees.emp_no, t.salary-h.salary as growth
from employees 
join salaries as h on employees.emp_no=h.emp_no
and employees.hire_date=h.from_date
join salaries as t on employees.emp_no=t.emp_no
and t.to_date='9999-01-01'
order by growth

解法二：先找出入职工资和离职工资的表，然后两表利用join连接，计算出离职工资减去入职工资。

select a.emp_no, b.t_salary-a.h_salary as growth
from 
(select employees.emp_no, salary as h_salary
from employees join salaries on employees.emp_no=salaries.emp_no
where from_date=hire_date) as a join
(select employees.emp_no, salary as t_salary
from employees join salaries on employees.emp_no=salaries.emp_no
where to_date='9999-01-01') as b on a.emp_no=b.emp_no
order by growth

输出结果如下：

SQL22 统计各个部门的工资记录数

题目：请你统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序。
部门表departments简况如下:

部门员工关系表dept_emp简况如下:

薪水表salaries简况如下:

解法：如上题（SQL19）所述，先按结果的表头来找dept_no、dept_name、sum（薪水数总和）各自存在的表，然后进行三表连接的排序，顺序为departments-dept_emp-salaries。

select departments.dept_no, departments.dept_name
,count(salary) as sum
from departments 
join dept_emp 
on departments.dept_no=dept_emp.dept_no
join salaries 
on dept_emp.emp_no=salaries.emp_no
group by dept_no
order by dept_no

结果：

SQL38 返回顾客名称和相关订单号以及每个订单的总价（SQL必知必会）

题目：除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。
解法：三表连接+group by，依然按前面所述，确定好三表连接的顺序，然后进行分组和排序。
题目链接，点此进入

此题最关键的是group by，很容易忽略掉order_num，然后会报错，虽然此题的cust_name和order_num是一一对应的，但如果是一对多的关系，就不能准确的进行分组。此处不仅要根据cust_name分组，还要根据order_num来进行分组。
因此，解决方案：看最终要求来判断哪几个来进行分组，比如此题，最终结果的表头是cust_name，order_num和OrderTotal，那就根据cust_name，order_num来进行分组。

select Customers.cust_name
,Orders.order_num
,sum(quantity*item_price) as OrderTotal
from Customers 
join Orders 
on Customers.cust_id=Orders.cust_id
join OrderItems 
on Orders.order_num=OrderItems.order_num
group by cust_name, order_num
order by cust_name, order_num

SQL41 确定最佳顾客的另一种方式（二）（SQL必知必会）

题目：编写 SQL 语句，返回订单总价不小于1000 的客户名称和总额（OrderItems 表中的order_num）。
提示：需要计算总和（item_price 乘以 quantity）。按订单号对结果进行排序，请使用INNER JOIN 语法。
题目链接，点此进入
解法：和SQL38（返回顾客名称和相关订单号以及每个订单的总价）解法差不多，但是group by有差异，此题需要最终结果的表头是cust_name和total_price，因此只需要对cust_name进行分组。

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

类型3：排序窗口函数

排序窗口函数语法：over([partition by 字段名] [order by 字段名 asc|desc])

三种用于进行排序的专用窗口函数：
1、RANK()
在计算排序时，若存在相同位次，会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，4······
2、DENSE_RANK()
这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
例如，有3条排在第1位时，排序为：1，1，1，2······
3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如，有3条排在第1位时，排序为：1，2，3，4······

偏移窗口函数语法：lag(字段名,偏移量[,默认值])over()

SQL23 对所有员工的薪水按照salary降序进行1-N的排名

题目：对所有员工的薪水按照salary降序先进行1-N的排名，如果salary相同，再按照emp_no升序排列。
题目链接，点此进入
薪水表salaries简况如下:

解法：直接套用排序函数，不需要分区（partition by），如果是要求每个部门的排序就要进行分区。

select emp_no
,salary
,dense_rank()over(order by salary desc) as t_rank
from salaries

LeetCode185 部门工资前三高的所有员工

题目：公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者是指一个员工的工资在该部门的 不同工资中排名前三 。编写一个SQL查询，找出每个部门中收入高的员工，以任意顺序返回结果表。
题目链接，点此进入

解法：这是两表连接且排序的问题，先利用join将两个表进行连接，然后对总表进行分组排序，上述操作构成一个新表a（mysql要求每一个派生出来的表都必须有一个自己的别名）。

select Department.name as Department
,Employee.name as Employee
,Salary
,dense_rank()over(partition by departmentId 
order by salary desc) as rk 
from Employee 
join Department on Employee.departmentId=Department.id

最后从新表a中来选取所需要的数据。

select Department, Employee, Salary
from (select Department.name as Department
,Employee.name as Employee
,Salary
,dense_rank()over(partition by departmentId 
order by salary desc) as rk 
from Employee 
join Department on Employee.departmentId=Department.id) as a 
where rk<=3

类型4：条件函数case when

语法：case when
case expr when v1 then r1 [when v2 then r2] …[else rn] end
例1：case 2 when 1 then ‘one’ when 2 then ‘two’ else ‘more’ end 返回two
case后面的值为2，与第二条分支语句when后面的值相等相等，因此返回two
例2：case when v1 then r1 [when v2 then r2]…[else rn] end
例如：case when 1<0 then ‘T’ else ‘F’ end返回F
1<0的结果为false，因此函数返回值为else后面的F

网易2018实习生招聘笔试题

题目链接，点此进入
题目：好评率是会员对平台评价的重要指标。现在需要统计2018年1月1日到2018年1月31日，用户’小明’提交的母婴类目"花王"品牌的好评率（好评率=“好评”评价量/总评价量）:
用户评价详情表：a
字段：id（评价id，主键），create_time（评价创建时间，格式’2017-01-01’）， user_name(用户名称)，goods_id(商品id，外键) ，
sub_time（评价提交时间，格式’2017-01-01 23:10:32’），sat_name（好评率类型，包含：“好评”、“中评”、“差评”）
商品详情表：b
字段：good_id（商品id，主键），bu_name（商品类目）, brand_name(品牌名称)

select sum(case when sat_time = '好评' then 1 else 0 end)/count(sat_time) as "好评率"
from a join b on a.good_id = b.good_id
where user_name = '小明' 
and (sub_time between '2017-1-1' and '2017-1-31')
and bu_name ='母婴' 
and brand_name = '花王'

SQL59 获取有奖金的员工相关信息

bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 to_date='9999-01-01’表示当前薪水。
请你给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。
bonus结果保留一位小数，输出结果按emp_no升序排序。
题目链接，点此进入
解法：简单的case when+三表连接的问题

select employees.emp_no, employees.first_name, employees.last_name
,emp_bonus.btype, salaries.salary
,(case when btype=1 then salary*0.1 
       when btype=2 then salary*0.2 
       else salary*0.3 end) as bonus
from employees join emp_bonus on employees.emp_no=emp_bonus.emp_no
join salaries on emp_bonus.emp_no=salaries.emp_no
where to_date='9999-01-01'
order by emp_no

SQL36 知乎问答高质量的用户属于各级别的数量

题目：回答字数大于等于100字的认为是高质量回答，请你统计某乎问答高质量的回答中用户属于1-2级、3-4级、5-6级的数量分别是多少，按数量降序排列
题目链接，点此进入

select
(case when author_level in (1,2) then '1-2级'
      when author_level in (3,4) then '3-4级'
      when author_level in (5,6) then '5-6级'
      end) as level_cut
, count(char_len) as num
from author_tb
join answer_tb
on author_tb.author_id=answer_tb.author_id
where char_len>=100
group by level_cut
order by num desc

类型5：累计求和（sum）窗口函数

语法：sum(字段名1) over(partition by 字段名2 order by 字段名3 asc/desc)

SQL60 统计salary的累计和running_total

题目：按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和，其他以此类推。
题目链接，点此进入

select emp_no
,salary
,sum(salary)over(order by emp_no)as running_total
from salaries
where to_date='9999-01-01'

结果如下：

类型6：交叉连接（笛卡儿积）

leetcode181 超过经理薪水的员工

题目链接，点此进入
题目：编写一个SQL查询来查找收入比经理高的员工。
Employee表：

解法1：自关联查询，先构建交叉链接，这是此类问题最主要的解法

select e1.Name as Employee 
from Employee e1, Employee e2
where e1.ManagerId = e2.Id AND e1.Salary > e2.Salary

解法2：子查询

select e.name as 'Employee' from Employee e
where salary>(select salary from Employee where ID=e.managerID)

LeetCode197 上升的温度

题目：编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。
返回结果 不要求顺序 。
题目链接，点此进入
Weather表：

step1：先构建交叉连接

setp2：datediff(日期1, 日期2)：
得到的结果是日期1与日期2相差的天数。
如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负。

select a.id from weather as a join weather as b
on a.temperature>b.temperature 
and datediff(a.recordDate,b.recordDate)=1

LeetCode196 删除重复的电子邮箱

题目：编写一个SQL查询来删除所有重复的电子邮件，只保留一个id最小的唯一电子邮件。以任意顺序返回结果表。
题目链接，点击进入
Person表如下:

Step2：对交叉连接的表进行观察并编写sql语句。

delete a from Person as a
join Person as b
on a.email=b.email
and a.id > b.id 

#或者以下写法
delete a from Person a, Person b
where a.email=b.email and a.id>b.id

类型7：用户连续登陆N天问题

SQL39 某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级

题目：知乎面试题
题目链接，点此进入
请你统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序），以上例子的输出结果如下：

解法：参考此文章SQL-用户连续登陆问题

利用排序窗口函数进行解答，对登陆日期进行排序，如果某个用户连续登陆，那么他的登陆日期-序号是相等的。

select t2.author_id, author_level, t2.day_cnt from
(select author_id, count(*) day_cnt from
(select answer_date, author_id
, dense_rank()over(partition by author_id order by answer_date) as rk
from answer_tb
group by answer_date,author_id) t1 #去重
group by author_id, date_sub(answer_date, interval rk day)
having count(*)>=3) t2
join author_tb
on t2.author_id=author_tb.author_id
order by t2.author_id

SQL29 某宝店铺连续2天及以上购物的用户及其对应的天数

11月结束后，小牛同学需要对其在某宝的网店就11月份用户交易情况和产品情况进行分析以更好的经营小店。
11月份销售数据表sales_tb如下（其中，sales_date表示销售日期，user_id指用户编号，item_id指货号，sales_num表示销售数量，sales_price表示结算金额）：

请你统计连续2天及以上在该店铺购物的用户及其对应的次数（若有多个用户，按user_id升序排序），以上例子的输出结果如下：

解法：和上题一样，用窗口函数进行排序然后用登陆日期减去序号

select user_id, count(*) as days_count from
(select sales_date, user_id
,dense_rank()over(partition by user_id order by sales_date) as rk
from sales_tb
group by sales_date, user_id) as t
group by user_id, date_sub(sales_date, interval rk day)
having count(*)>=2

类型8：中位数问题

SQL88 SQL88 最差是第几名(二)

题目链接，点此进入
题目：老师想知道学生们综合成绩的中位数是什么档位，请你写SQL帮忙查询一下，如果只有1个中位数，输出1个，如果有2个中位数，按grade升序输出，以上例子查询结果如下:

解析:
总体学生成绩排序如下:A, A, B, B, B, B, C, C, C, C, D, D，总共12个数，取中间的2个，取6，7为:B,C

解法：首先我们需要知道:当某一数的正序和逆序累计均大于整个序列的数字个数的一半即为中位数

select grade
from
(select grade
, (select sum(number) from class_grade) as total
, sum(number)over(order by grade) as a 
, sum(number)over(order by grade desc) as b
from class_grade) t 
where a>=total/2 and b>=total/2
order by grade

更新于2022/4/9