【SQL】刷题整理[四]

一、力扣176. 第二高的薪水

解决NULL的问题需要用到IFNULL的函数

代码如下：

select 
    IFNULL(
        (select distinct Salary
        from Employee
        order by Salary DESC
        limit 1 offset 1),
        null
    ) as SecondHighestSalary

二、力扣177. 第N高的薪水

代码如下：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    SET n := n-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary 
      from Employee
      order by Salary desc
      limit n,1
  );
END

三、力扣577. 员工奖金

代码如下：

select a.name,b.bonus
from Employee as a left join Bonus as b
on a.empId = b.empId
where bonus is null or bonus <1000;

四、力扣586. 订单最多的客户

代码如下：

select customer_number
from orders
group by customer_number
order by count(*) desc
limit 1;

五、力扣181. 超过经理收入的员工

代码如下：

select a.name as Employee
from Employee as a,Employee as b
where a.ManagerID = b.Id and a.Salary > b.Salary;

✨✨✨
旺财要加油呀！