[SQL知识点]LeetCode详解（175-185）

目录

175.组合两个表

176.第二高的薪水

177.第N高的薪水

 178.分数排名

180.连续出现的数字

181.超过经理薪资的员工

182.查找重复的电子邮箱

183.从不订购的用户

184.部门工资最高的员工

185.部门工资前三高的所有员工

---

175.组合两个表

select firstName,lastName,city,state
from Person a left join Address b on a.personId=b.personId

176.第二高的薪水

解法一：dense_rank+开窗函数

select ifnull((select salary
from (
    select distinct(salary),dense_rank() over (order by salary desc) as rk
    from Employee) b
where rk = 2),null) as SecondHighestSalary

tip1：使用开窗函数，可以对薪水生成新的排序列

其中sql中用于生成排序编号的函数有row_number()、rank()、dense_rank()

三者区别为：

• row_number：顺序生成，一样的数也会区分顺序
• rank：一样的数一样的编号，但接下来的编号会跳号
• dense_rank：一样的数一样的编号，但接下来的编号不会跳号

由于该题需要筛选第二高的薪水，则编号要为2，考虑到如果薪水为：100 100 50，则50的编号应为2，因此需要使用dense_rank（不跳号）

tip2：ifnull函数

IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。 否则，IFNULL函数返回第二个参数。

由于该题可能出现薪水：100 100的情况，也就是没有第二高的薪水，则需要返回null，因此需要用到ifnull函数

解法二：limit

select ifnull(
    (select distinct(salary) from Employee
    order by salary desc
    limit 1,1)   
    ,null) as SecondHighestSalary

177.第N高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      select ifnull((
          select salary from(   
          select distinct(salary),dense_rank() over(order by salary desc) as rk
          from Employee) b where rk = N),null) as getNthHighestSalary
  );
END

 178.分数排名

select score,dense_rank() over(order by score desc) as 'rank'
from Scores

tip1：rank也为函数，因此重命名时需要加引号

180.连续出现的数字

SELECT DISTINCT Num AS ConsecutiveNums FROM Logs 
WHERE (Id+1, Num) IN (SELECT * FROM Logs)
AND (Id+2, Num) IN (SELECT * FROM Logs)

181.超过经理薪资的员工

select a.name 'Employee'
from Employee a left join Employee b on a.managerId = b.id
where a.salary>b.salary

182.查找重复的电子邮箱

select Email
from Person
group by Email
having count(Id)>1

183.从不订购的用户

select name as Customers
from Customers
where Id not in (select distinct(CustomerId) from Orders)

184.部门工资最高的员工

select Department,Employee,Salary
from (select b.name 'Department',a.name 'Employee',salary 'Salary',rank() over(partition by b.name order by salary desc) as rk
from Employee a left join Department b on a.departmentId=b.id) c
where rk = 1

tip1：使用开窗函数，按部门进行排序，再取1

185.部门工资前三高的所有员工

select Department,Employee,Salary
from (select b.name 'Department',a.name 'Employee',salary 'Salary',dense_rank() over(partition by b.name order by salary desc) rk
from Employee a left join Department b on a.departmentId = b.id) c
where rk<4