MySQL 将FROM中的子查询称为派生表(Derived Table)。以下查询使用了一个派生表:
SELECT * FROM (SELECT 1) AS dt;
+---+
| 1 |
+---+
| 1 |
+---+
不过,MySQL 中的派生表存在一些限制:
派生表不能是关联子查询
派生表不能引用它所在的SELECT语句中的其他表
在 MySQL 8.0.14 之前,派生表不能引用它所在的SELECT语句外部的表
简单来说,就是派生表必须能够单独运行,而不能依赖其他表。
从 MySQL 8.0.14 开始,派生表支持LATERAL关键字前缀,表示允许派生表引用它所在的FROM子句中的其他表。横向派生表能够完成普通派生表无法完成或者效率低下的操作。
考虑以下应用场景:departments 表存储了部门的信息,employees 表存储了员工信息。如何查找每个部门中薪水最高的 Top 5 和对应的员工?
示例表和数据
首先,使用传统的方法很难实现这样的功能:
SELECT d.department_name,
(SELECT e.salary
FROM employees e
WHERE e.department_id = d.department_id
ORDER BY e.salary DESC LIMIT 5
)
FROM departments d;
ERROR 1242 (21000): Subquery returns more than 1 row
以上查询失败的原因在于SELECT子查询只能返回 1 条数据。
按照需求,我们可以先按照部门编号对员工信息进行分组,获得每个组内的薪水最高的 5 个员工,然后和部门表进行连接查询:
SELECT d.department_name, t.first_name, t.last_name, t.salary
FROM departments d
LEFT JOIN (SELECT e.department_id, e.first_name, e.last_name, e.salary