MySQL 相关子查询以及横向派生表
子查询是嵌套在外部查询中的查询。相关子查询使用外部查询的值。每当外部查询处理一行数据时,就针对该行数据执行一次相关子查询,因此其结果依赖外部查询中正在处理的行。
相关子查询可以解决 GROUP BY 的一些限制 - 只能查询分组字段和聚合字段。但是有时这些字段无法满足需求,所以需要用相关子查询解决这些限制。
员工表如下:
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)
求:每个部门工资最高的员工及其工资。
如果只求 Department
和 MaxSalary
,那么只需按部门进行分组,计算 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;
在 MySQL 中,派生表通常不能在同一个 FROM 子句中引用(依赖)前面的表中的列。在 MySQL 8.0.14 中,派生表可被定义为横向(LATERAL)派生表,以允许这种引用。其实就是通过关键字 LATERAL 在子查询的结果集中引用外层表达式的列。这种使用方式有助于简化查询语句,提高查询效率。
横向派生表受以下限制:
JSON_TABLE()
)的联接视为使用 LATERAL。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;
因为是子查询,所以必然生成中间表,因此如非必要不要使用横向派生表。