MySQL 8.0 新特性之横向（LATERAL）派生表_mysql

概述MySQL 将FROM子句中的子查询称为派生表（Derived Table）。从 MySQL 8.0.14 开始，派生表支持 LATERAL 关键字前缀，表示允许派生表引用它所在的 FROM 子句中的其他表。横向派生表能够完成普通派生表无法完成或者效率低下的 *** 作。

文章目录

我在网发布的免费视频讲解 MySQL 8.0 版本新特性。

MysqL 将FROM中的子查询称为派生表（Derived table）。以下查询使用了一个派生表：

SELECT * FROM (SELECT @H_403_26@1) AS dt;+---+| @H_403_26@1 |+---+| @H_403_26@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 @H_403_26@5       )  FROM departments d;ERROR @H_403_26@1242 (@H_403_26@21000): Subquery returns more than @H_403_26@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               FROM employees e              WHERE e.department_ID = d.department_ID              ORDER BY e.salary DESC liMIT @H_403_26@5) t    ON d.department_ID = t.department_ID  ORDER BY d.department_name, t.salary DESC;ERROR @H_403_26@1054 (@H_403_26@42S22): UnkNown column 'd.department_ID' in 'where clause'

以上语句失败的原因在于子查询 t 不能引用外部查询中的 departments 表。

可以利用 MysqL 中的自定义变量实现该功能：

SELECT d.department_name, w.first_name, w.last_name, w.salary, w.rn  FROM departments d  left JOIN (        SELECT *          FROM (               SELECT a.*, if(@dID = a.department_ID, @rn := @rn+@H_403_26@1, @rn := @H_403_26@1) AS rn, @dID := a.department_ID AS dID                 FROM (SELECT e.* FROM employees e ORDER BY department_ID, salary DESC) a,		              (SELECT @rn := @H_403_26@0 rn, @dID := @H_403_26@0) b               ) AS t         WHERE t.rn <= @H_403_26@5        ) AS w    ON d.department_ID = w.department_ID  ORDER BY d.department_name, w.salary DESC;+----------------------+-------------+-----------+----------+------+| department_name      | first_name  | last_name | salary   | rn   |+----------------------+-------------+-----------+----------+------+| Accounting           | Shelley     | Higgins   | @[email protected] |    @H_403_26@1 || Accounting           | William     | GIEtz     |  @[email protected] |    @H_403_26@2 || administration       | Jennifer    | Whalen    |  @[email protected] |    @H_403_26@1 || Benefits             | NulL        | NulL      |     NulL | NulL || Construction         | NulL        | NulL      |     NulL | NulL || Contracting          | NulL        | NulL      |     NulL | NulL || Control And Credit   | NulL        | NulL      |     NulL | NulL || Corporate Tax        | NulL        | NulL      |     NulL | NulL || Executive            | Steven      | King      | @[email protected] |    @H_403_26@1 || Executive            | Neena       | Kochhar   | @[email protected] |    @H_403_26@2 || Executive            | Lex         | De Haan   | @[email protected] |    @H_403_26@3 || Finance              | Nancy       | Greenberg | @[email protected] |    @H_403_26@1 || Finance              | DanIEl      | FavIEt    |  @[email protected] |    @H_403_26@2 || Finance              | John        | Chen      |  @[email protected] |    @H_403_26@3 || Finance              | Jose Manuel | Urman     |  @[email protected] |    @H_403_26@4 || Finance              | Ismael      | Sciarra   |  @[email protected] |    @H_403_26@5 || Government Sales     | NulL        | NulL      |     NulL | NulL || Human Resources      | Susan       | Mavris    |  @[email protected] |    @H_403_26@1 |...@H_403_26@51 rows in set (@[email protected] sec)

可以看出，这种方法比较复杂，也不具有通用性。

现在来看一下如何使用LAteraL派生表实现这个需求：

SELECT d.department_name, t.salary  FROM departments d  left JOIN LAteraL (SELECT e.department_ID, t.salary DESC;+----------------------+-------------+-----------+----------+------+| department_name      | first_name  | last_name | salary   | rn   |+----------------------+-------------+-----------+----------+------+| Accounting           | Shelley     | Higgins   | @[email protected] |    @H_403_26@1 || Accounting           | William     | GIEtz     |  @[email protected] |    @H_403_26@2 || administration       | Jennifer    | Whalen    |  @[email protected] |    @H_403_26@1 || Benefits             | NulL        | NulL      |     NulL | NulL || Construction         | NulL        | NulL      |     NulL | NulL || Contracting          | NulL        | NulL      |     NulL | NulL || Control And Credit   | NulL        | NulL      |     NulL | NulL || Corporate Tax        | NulL        | NulL      |     NulL | NulL || Executive            | Steven      | King      | @[email protected] |    @H_403_26@1 || Executive            | Neena       | Kochhar   | @[email protected] |    @H_403_26@2 || Executive            | Lex         | De Haan   | @[email protected] |    @H_403_26@3 || Finance              | Nancy       | Greenberg | @[email protected] |    @H_403_26@1 || Finance              | DanIEl      | FavIEt    |  @[email protected] |    @H_403_26@2 || Finance              | John        | Chen      |  @[email protected] |    @H_403_26@3 || Finance              | Jose Manuel | Urman     |  @[email protected] |    @H_403_26@4 || Finance              | Ismael      | Sciarra   |  @[email protected] |    @H_403_26@5 || Government Sales     | NulL        | NulL      |     NulL | NulL || Human Resources      | Susan       | Mavris    |  @[email protected] |    @H_403_26@1 |...@H_403_26@51 rows in set (@[email protected] sec)

以上语句基于前面的一个失败示例，只是在left JOIN之后，派生表之前加上一个LAteraL关键字，使得子查询 t 能够引用前面的 departments 表。这种方法能够实现很多类似的复杂功能，简单而且高效。

MysqL 从 8.0.14 开始支持横向派生表，同时存在以下限制：

横向派生表只能出现在FROM子句中，包括使用逗号分隔的表或者标准的连接语句（JOIN、INNER JOIN、CROSS JOIN、left [OUTER] JOIN以及RIGHT [OUTER] JOIN）。

如果横向派生表位于连接 *** 作的右侧，并且引用了左侧的表，连接类型必须为INNER JOIN、CROSS JOIN或者left [OUTER] JOIN。

如果横向派生表位于连接 *** 作的左侧，并且引用了右侧的表，连接类型必须为INNER JOIN、CROSS JOIN或者RIGHT [OUTER] JOIN。

如果横向派生表引用了聚合函数，那么该函数的聚合查询语句不能是横向派生表所在的FROM子句所属的查询语句。

根据 sql 标准，表函数拥有一个隐式的LAteraL，这与 MysqL 8.0 到 MysqL 8.0.14 之前版本的实现一致。但是，根据标准，函数 JsON_table() 之前不能存在LAteraL关键字，包括隐式的LAteraL。

当然，也可以使用 MysqL 8.0 中新增的窗口函数完成相同的功能：

SELECT *  FROM (SELECT d.department_name, e.salary,               row_number() OVER (PARTITION BY d.department_ID ORDER BY e.salary DESC) AS rn          FROM departments d          left JOIN employees e            ON (e.department_ID = d.department_ID)       ) AS t WHERE t.rn <= @H_403_26@5 ORDER BY t.department_name,t.rn;+----------------------+-------------+-----------+----------+----+| department_name      | first_name  | last_name | salary   | rn |+----------------------+-------------+-----------+----------+----+| Accounting           | Shelley     | Higgins   | @[email protected] |  @H_403_26@1 || Accounting           | William     | GIEtz     |  @[email protected] |  @H_403_26@2 || administration       | Jennifer    | Whalen    |  @[email protected] |  @H_403_26@1 || Benefits             | NulL        | NulL      |     NulL |  @H_403_26@1 || Construction         | NulL        | NulL      |     NulL |  @H_403_26@1 || Contracting          | NulL        | NulL      |     NulL |  @H_403_26@1 || Control And Credit   | NulL        | NulL      |     NulL |  @H_403_26@1 || Corporate Tax        | NulL        | NulL      |     NulL |  @H_403_26@1 || Executive            | Steven      | King      | @[email protected] |  @H_403_26@1 || Executive            | Neena       | Kochhar   | @[email protected] |  @H_403_26@2 || Executive            | Lex         | De Haan   | @[email protected] |  @H_403_26@3 || Finance              | Nancy       | Greenberg | @[email protected] |  @H_403_26@1 || Finance              | DanIEl      | FavIEt    |  @[email protected] |  @H_403_26@2 || Finance              | John        | Chen      |  @[email protected] |  @H_403_26@3 || Finance              | Jose Manuel | Urman     |  @[email protected] |  @H_403_26@4 || Finance              | Ismael      | Sciarra   |  @[email protected] |  @H_403_26@5 || Government Sales     | NulL        | NulL      |     NulL |  @H_403_26@1 || Human Resources      | Susan       | Mavris    |  @[email protected] |  @H_403_26@1 |...@H_403_26@51 rows in set (@[email protected] sec)