• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 横向派生表

    派生表通常不能引用(取决于)同一FROM子句中先前表的列。从MySQL 8.0.14开始,派生表可以定义为横向派生表,以指定允许此类引用

    使用“派生表”中讨论的语法来指定非边派生表。侧面派生表的语法与非侧面派生表的语法相同,只是LATERAL在派生表指定之前指定了关键字。的LATERAL关键字必须先于每个表以用作横向派生表。

    横向派生表受以下限制:

    • 可发生横向派生表只在一个FROM条款,无论是在用逗号或在分离的表的列表加入规范(JOININNER JOINCROSS JOINLEFT[OUTER] JOIN,或RIGHT[OUTER] JOIN)。
    • 如果横向派生表是在右操作数的连接子句和包含对左操作数的引用,所述加入操作必须是INNER JOINCROSS JOIN,或LEFT[OUTER] JOIN

      如果表是在左边的操作数,并包含右操作数的引用,连接操作必须是INNER JOINCROSS JOINRIGHT[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 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是针对上述两种方法中所有缺点的有效解决方案。


    上篇:派生表

    下篇:子查询错误