牛客网SQL刷题笔记（MySQL）

牛客网SQL刷题笔记（MySQL）

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

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

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

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

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01
解法1：子查询+distinct。

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简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01
10003	d002	1990-08-05	9999-01-01

薪水表salaries简况如下:

emp_no	salary	from_date	to_date
1001	88958	2002-06-22	9999-01-01
1002	72527	2001-08-22	9999-01-01
1003	43311	2001-12-01	9999-01-01

解法：因为每个部门可能有多个最大的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简况如下：

emp_no	salary	from_date	to_date
1001	88958	2002-06-22	9999-01-01
1002	72527	2001-08-22	9999-01-01
1003	43311	2001-12-01	9999-01-01

解法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简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01

薪水表salaries简况如下:

emp_no	salary	from_date	to_date
1001	88958	2002-06-22	9999-01-01
1002	72527	2001-08-22	9999-01-01
1003	43311	2001-12-01	9999-01-01
1004	74057	2001-11-27	9999-01-01

解法：内连接+子查询+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)