我在网发布的免费视频讲解 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 | @H_403_26@12008.00 | @H_403_26@1 || Accounting | William | GIEtz | @H_403_26@8300.00 | @H_403_26@2 || administration | Jennifer | Whalen | @H_403_26@4400.00 | @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 | @H_403_26@24000.00 | @H_403_26@1 || Executive | Neena | Kochhar | @H_403_26@17000.00 | @H_403_26@2 || Executive | Lex | De Haan | @H_403_26@17000.00 | @H_403_26@3 || Finance | Nancy | Greenberg | @H_403_26@12008.00 | @H_403_26@1 || Finance | DanIEl | FavIEt | @H_403_26@9000.00 | @H_403_26@2 || Finance | John | Chen | @H_403_26@8200.00 | @H_403_26@3 || Finance | Jose Manuel | Urman | @H_403_26@7800.00 | @H_403_26@4 || Finance | Ismael | Sciarra | @H_403_26@7700.00 | @H_403_26@5 || Government Sales | NulL | NulL | NulL | NulL || Human Resources | Susan | Mavris | @H_403_26@6500.00 | @H_403_26@1 |...@H_403_26@51 rows in set (@H_403_26@0.00 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 | @H_403_26@12008.00 | @H_403_26@1 || Accounting | William | GIEtz | @H_403_26@8300.00 | @H_403_26@2 || administration | Jennifer | Whalen | @H_403_26@4400.00 | @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 | @H_403_26@24000.00 | @H_403_26@1 || Executive | Neena | Kochhar | @H_403_26@17000.00 | @H_403_26@2 || Executive | Lex | De Haan | @H_403_26@17000.00 | @H_403_26@3 || Finance | Nancy | Greenberg | @H_403_26@12008.00 | @H_403_26@1 || Finance | DanIEl | FavIEt | @H_403_26@9000.00 | @H_403_26@2 || Finance | John | Chen | @H_403_26@8200.00 | @H_403_26@3 || Finance | Jose Manuel | Urman | @H_403_26@7800.00 | @H_403_26@4 || Finance | Ismael | Sciarra | @H_403_26@7700.00 | @H_403_26@5 || Government Sales | NulL | NulL | NulL | NulL || Human Resources | Susan | Mavris | @H_403_26@6500.00 | @H_403_26@1 |...@H_403_26@51 rows in set (@H_403_26@0.00 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 | @H_403_26@12008.00 | @H_403_26@1 || Accounting | William | GIEtz | @H_403_26@8300.00 | @H_403_26@2 || administration | Jennifer | Whalen | @H_403_26@4400.00 | @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 | @H_403_26@24000.00 | @H_403_26@1 || Executive | Neena | Kochhar | @H_403_26@17000.00 | @H_403_26@2 || Executive | Lex | De Haan | @H_403_26@17000.00 | @H_403_26@3 || Finance | Nancy | Greenberg | @H_403_26@12008.00 | @H_403_26@1 || Finance | DanIEl | FavIEt | @H_403_26@9000.00 | @H_403_26@2 || Finance | John | Chen | @H_403_26@8200.00 | @H_403_26@3 || Finance | Jose Manuel | Urman | @H_403_26@7800.00 | @H_403_26@4 || Finance | Ismael | Sciarra | @H_403_26@7700.00 | @H_403_26@5 || Government Sales | NulL | NulL | NulL | @H_403_26@1 || Human Resources | Susan | Mavris | @H_403_26@6500.00 | @H_403_26@1 |...@H_403_26@51 rows in set (@H_403_26@0.00 sec)相关文档：
MySQL 8.0 横向派生表
其他数据库产品的功能实现：
Oracle 18c 横向（LATERAL）内联视图
Oracle 18c APPLY 连接
SQL Server 2017 APPLY 运算符
PostgreSQL 11 横向（LATERAL）子查询
Db2 11 横向（LATERAL）连接
人生本来短暂，你又何必匆匆！点个赞再走吧！
总结以上是内存溢出为你收集整理的MySQL 8.0 新特性之横向（LATERAL）派生表全部内容，希望文章能够帮你解决MySQL 8.0 新特性之横向（LATERAL）派生表所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）