力扣刷题 ｜ 数据库

本文内容来自 leetcode
SQL 基础教程参考 w3school

175 组合两个表

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State

解法： 左连接

SELECT FirstName,
       LastName,
       City,
       STATE
FROM Person
LEFT JOIN Address ON Person.PersonId = Address.PersonId

176 第二高的薪水

编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

排名的三种方法：

• 300 200 200 100 为 1 2 3 4
• 300 200 200 100 为 1 2 2 4
• 300 200 200 100 为 1 2 2 3

本题为第三种

解法1： 嵌套查询，先查出最大值，再查出小于最大值里的最大值

SELECT max(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary <
    (SELECT max(Salary)
     FROM Employee)

解法2： 采用 limit 和 offset 方法，外层是为了保证只有一条数据时可以返回空值

SELECT
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary

177 第N高的薪水

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

解法1： limit 和 offset 方法

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    Set N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT Salary
      FROM Employee
      GROUP BY Salary
      ORDER BY Salary DESC LIMIT 1
      OFFSET N
  );
END

解法2： 排名第N的薪水意味着该表中存在N-1个比其更高的薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT salary
      FROM Employee e
      WHERE
          (SELECT count(DISTINCT salary)
          FROM Employee
          WHERE salary > e.salary) = N-1
  );
END

解法3： 自连接，把每个薪水和大于等于这个薪水的值连起来，按照第一个薪水分组，找到具有 N 个不同的第二个薪水的值的第一个薪水

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT e1.salary
      FROM Employee e1
      LEFT JOIN Employee e2 ON e1.salary <= e2.salary
      GROUP BY e1.salary HAVING count(DISTINCT e2.salary) = N
  );
END

解法4： 笛卡尔积，和解法3相比是用子查询替代了连接

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT e1.salary
      FROM Employee e1, Employee e2
      where e1.salary <= e2.salary
      GROUP BY e1.salary HAVING count(DISTINCT e2.salary) = N
  );
END

解法5： 定义变量

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      SELECT DISTINCT salary
      FROM
      (SELECT salary, @r:=IF(@p=salary, @r, @r+1) AS rnk, @p:=salary
      FROM Employee,
          (SELECT @r:=0, @p:= NULL) init
      ORDER BY salary DESC) tmp
      WHERE rnk = N
  );
END

解法6： 窗口函数，在 mysql8.0 中有相关的内置函数，而且考虑了各种排名问题：

• row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
• rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
• dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
• ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用

前三个函数必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是

• partition by，按某字段切分
• order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据

CREATE FUNCTION getNthHighestSalary(