MySQL 相关子查询以及横向派生表


1. 相关子查询

1.1. 什么是相关子查询

子查询是嵌套在外部查询中的查询。相关子查询使用外部查询的值。每当外部查询处理一行数据时,就针对该行数据执行一次相关子查询,因此其结果依赖外部查询中正在处理的行。

1.2. 为什么使用相关子查询

相关子查询可以解决 GROUP BY 的一些限制 - 只能查询分组字段和聚合字段。但是有时这些字段无法满足需求,所以需要用相关子查询解决这些限制。

1.3. 示例

员工表如下:

mysql> show create table Employees\G
*************************** 1. row ***************************
       Table: Employees
Create Table: CREATE TABLE `Employees` (
  `EmployeeID` int DEFAULT NULL,
  `Department` varchar(50) DEFAULT NULL,
  `Salary` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select * from Employees;
+------------+-------------+-----------+
| EmployeeID | Department  | Salary    |
+------------+-------------+-----------+
|          1 | Sales       |  60000.00 |
|          2 | Engineering |  70000.00 |
|          3 | IT          |  80000.00 |
|          4 | IT          |  90000.00 |
|          5 | IT          |  40000.00 |
|          6 | Engineering | 100000.00 |
|          7 | Sales       |  20000.00 |
+------------+-------------+-----------+
7 rows in set (0.00 sec)

求:每个部门工资最高的员工及其工资。

如果只求 DepartmentMaxSalary,那么只需按部门进行分组,计算 MAX(Salary) 即可。但是现在除这两个字段外还需要 EmployeeID 字段,该字段不是分组字段,所以无法使用 GROUP BY 直接查询,此时需要使用相关子查询。

SELECT Department, EmployeeID, Salary
FROM Employees e
WHERE Salary IN (SELECT MAX(Salary)
                 FROM Employees
                 WHERE Department = e.Department);

对于该需求而言,除使用相关子查询外,还可以使用如下两个 SQL 实现:

SELECT Department, EmployeeID, MAX(Salary)
FROM (SELECT e1.Department, e1.EmployeeID, e1.Salary, e2.EmployeeID as e2_employee_id
      FROM Employees e1
               LEFT JOIN Employees e2 ON e1.Department = e2.Department AND e1.Salary < e2.Salary) t
GROUP BY Department, EmployeeID
HAVING COUNT(e2_employee_id) < 1;

SELECT Department, EmployeeID, Salary
FROM (SELECT Department, EmployeeID, Salary, RANK() OVER ( PARTITION BY Department ORDER BY Salary DESC ) AS nth
      FROM Employees) t
WHERE nth <= 1;

1.4. 总结


2. 横向派生表

2.1. 介绍

在 MySQL 中,派生表通常不能在同一个 FROM 子句中引用(依赖)前面的表中的列。在 MySQL 8.0.14 中,派生表可被定义为横向(LATERAL)派生表,以允许这种引用。其实就是通过关键字 LATERAL 在子查询的结果集中引用外层表达式的列。这种使用方式有助于简化查询语句,提高查询效率。

横向派生表受以下限制:

2.2. 示例:使用横向派生表实现 1.3

SELECT Department, EmployeeID, Salary, e2.MaxSalary
FROM Employees e1
         INNER JOIN LATERAL (
    SELECT MAX(e3.Salary) AS MaxSalary FROM Employees e3 WHERE e3.Department = e1.Department
    ) e2 ON e1.Salary = e2.MaxSalary;

2.3. 并非所有场景都适合使用横向派生表

因为是子查询,所以必然生成中间表,因此如非必要不要使用横向派生表。


参考文档