Documentation Home
MySQL 8.0 参考手册

13.2.11.9 横向派生表

派生表通常不能引用（依赖于）同一FROM子句中前面表的列。从 MySQL 8.0.14 开始，派生表可以定义为横向派生表，以指定允许此类引用。

非横向派生表使用第 13.2.11.8 节“派生表” 中讨论的语法指定。横向派生表的语法与非横向派生表的语法相同，只是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 标准，MySQL 始终将与表函数的连接视为 JSON_TABLE()已 LATERAL使用过。无论 MySQL 发布版本如何，都是如此，这就是为什么即使在 8.0.14 之前的 MySQL 版本中也可以加入此功能。在 MySQL 8.0.14 及之后的版本中，该 LATERAL关键字是隐式的，在 之前是不允许的JSON_TABLE()。这也是符合 SQL 标准的。

以下讨论展示了横向派生表如何使某些 SQL 操作成为可能，而这些操作无法使用非横向派生表完成，或者需要低效的变通方法。

假设我们要解决这个问题：给定一张销售人员表（其中每一行描述销售人员的一名成员）和一张所有销售额的表（其中每一行描述一次销售：销售人员、客户、金额, 日期), 确定每个销售人员的最大销售额的规模和客户。这个问题可以通过两种方式来解决。

解决问题的第一种方法：对于每个销售人员，计算最大销售量，并找出提供这个最大量的客户。在 MySQL 中，可以这样做： 

SELECT
  salesperson.name,
  -- find maximum sale size for this salesperson
  (SELECT MAX(amount) AS amount
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id)
  AS amount,
  -- find customer for this maximum size
  (SELECT customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    AND all_sales.amount =
         -- find maximum size, again
         (SELECT MAX(amount) AS amount
           FROM all_sales
           WHERE all_sales.salesperson_id = salesperson.id))
  AS customer_name
FROM
  salesperson;

该查询效率低下，因为它为每个销售人员计算两次最大大小（一次在第一个子查询中，一次在第二个子查询中）。

我们可以尝试通过计算每个销售人员一次的最大值并将其“缓存”在派生​​表中来提高效率，如以下修改后的查询所示： 

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale_customer.customer_name
FROM
  salesperson,
  -- calculate maximum size, cache it in transient derived table max_sale
  (SELECT MAX(amount) AS amount
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id)
  AS max_sale,
  -- find customer, reusing cached maximum size
  (SELECT customer_name
    FROM all_sales
    WHERE 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_name
FROM
  salesperson,
  -- calculate maximum size, cache it in transient derived table max_sale
  LATERAL
  (SELECT MAX(amount) AS amount
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id)
  AS max_sale,
  -- find customer, reusing cached maximum size
  LATERAL
  (SELECT customer_name
    FROM all_sales
    WHERE 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_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC LIMIT 1)
FROM
  salesperson;

那是有效但非法的。它不起作用，因为这样的子查询只能返回一个列： 

ERROR 1241 (21000): Operand should contain 1 column(s)

重写查询的一种尝试是从派生表中选择多个列： 

SELECT
  salesperson.name,
  max_sale.amount,
  max_sale.customer_name
FROM
  salesperson,
  -- find maximum size and customer at same time
  (SELECT amount, customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC 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_name
FROM
  salesperson,
  -- find maximum size and customer at same time
  LATERAL
  (SELECT amount, customer_name
    FROM all_sales
    WHERE all_sales.salesperson_id = salesperson.id
    ORDER BY amount DESC LIMIT 1)
  AS max_sale;

简而言之，LATERAL这是解决刚刚讨论的两种方法中所有缺点的有效解决方案。