横向派生表
派生表通常不能引用(取决于)同一FROM
子句中先前表的列。从MySQL 8.0.14开始,派生表可以定义为横向派生表,以指定允许此类引用。
使用“派生表”中讨论的语法来指定非边派生表。侧面派生表的语法与非侧面派生表的语法相同,只是LATERAL
在派生表指定之前指定了关键字。的LATERAL
关键字必须先于每个表以用作横向派生表。
横向派生表受以下限制:
- 可发生横向派生表只在一个
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
,因此其行为与8.0.14之前的MySQL 8.0版本相同。但是,根据标准,即使该LATERAL
单词JSON_TABLE()
是隐含的,也不允许在单词before之前使用。
以下讨论显示了横向派生表如何使某些SQL操作成为可能,这些操作无法通过非横向派生表完成或需要效率较低的解决方法。
假设我们要解决此问题:给定销售人员表(每行描述销售人员),以及所有销售表(每行描述销售):销售人员,客户,金额(日期),确定每个销售人员的最大销售额的规模和客户。此问题可以通过两种方式解决。
解决问题的第一种方法:为每个销售人员计算最大销售规模,并找到提供该最大销售量的客户。在MySQL中,可以这样进行:
SELECT salesperson.name , -- find maximum sale size for this salesperson (SELECT MAX(amount)AS amountFROM all_salesWHERE all_sales.salesperson_id = salesperson.id)AS amount, -- find customer for this maximum size (SELECT customer_nameFROM all_salesWHERE all_sales.salesperson_id = salesperson.id AND all_sales.amount = -- find maximum size, again (SELECT MAX(amount)AS amountFROM all_salesWHERE all_sales.salesperson_id = salesperson.id))AS customer_nameFROM salesperson;
该查询效率不高,因为它为每个销售员计算两次最大大小(第一次在子查询中一次,第二次在子查询中一次)。
我们可以尝试通过每个销售人员计算一次最大值并将其“缓存”在派生表中来实现效率提高,如以下修改后的查询所示:
SELECT salesperson.name , max_sale.amount, max_sale_customer.customer_nameFROM salesperson, -- calculate maximum size, cache it in transient derived table max_sale (SELECT MAX(amount)AS amountFROM all_salesWHERE all_sales.salesperson_id = salesperson.id)AS max_sale, -- find customer, reusing cached maximum size (SELECT customer_nameFROM all_salesWHERE all_sales.salesperson_id = salesperson.id AND all_sales.amount = -- the cached maximum size max_sale.amount)AS max_sale_customer;
但是,该查询在SQL-92中是非法的,因为派生表不能依赖同一FROM
子句中的其他表。派生表在查询期间必须是恒定的,不能包含对其他FROM
子句表的列的引用。如所写,查询将产生以下错误:
ERROR 1054 (42S22): Unknown column 'salesperson.id' in 'where clause'
在SQL:1999中,如果派生表前面带有LATERAL
关键字(这意味着“此派生表取决于其左侧的先前表”),则查询变为合法:
SELECT salesperson.name , max_sale.amount, max_sale_customer.customer_nameFROM salesperson, -- calculate maximum size, cache it in transient derived table max_saleLATERAL (SELECT MAX(amount)AS amountFROM all_salesWHERE all_sales.salesperson_id = salesperson.id)AS max_sale, -- find customer, reusing cached maximum sizeLATERAL (SELECT customer_nameFROM all_salesWHERE all_sales.salesperson_id = salesperson.id AND all_sales.amount = -- the cached maximum size max_sale.amount)AS max_sale_customer;
横向派生表不必是恒定的,并且每次由顶部查询处理前一个表所依赖的新行时,都会使该派生表保持最新。
解决问题的第二种方法:如果SELECT
列表中的子查询可以返回多列,则可以使用不同的解决方案:
SELECT salesperson.name , -- find maximum size and customer at same time (SELECT amount, customer_nameFROM all_salesWHERE all_sales.salesperson_id = salesperson.idORDER BY amountDESC LIMIT 1)FROM salesperson;
那是有效的,但是是非法的。它不起作用,因为此类子查询只能返回单个列:
ERROR 1241 (21000): Operand should contain 1 column(s)
重写查询的一种尝试是从派生表中选择多个列:
SELECT salesperson.name , max_sale.amount, max_sale.customer_nameFROM salesperson, -- find maximum size and customer at same time (SELECT amount, customer_nameFROM all_salesWHERE all_sales.salesperson_id = salesperson.idORDER BY amountDESC LIMIT 1)AS max_sale;
但是,这也不起作用。派生表依赖于该salesperson
表,因此在没有以下情况下将失败LATERAL
:
ERROR 1054 (42S22): Unknown column 'salesperson.id' in 'where clause'
添加LATERAL
关键字使查询合法:
SELECT salesperson.name , max_sale.amount, max_sale.customer_nameFROM salesperson, -- find maximum size and customer at same timeLATERAL (SELECT amount, customer_nameFROM all_salesWHERE all_sales.salesperson_id = salesperson.idORDER BY amountDESC LIMIT 1)AS max_sale;
简而言之,LATERAL
是针对上述两种方法中所有缺点的有效解决方案。