MySQL的SQL语句 - 数据操作语句(13)- 子查询(10)

横向派生表

派生表通常不能引用(依赖)同一 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 版本中相同。但是,根据标准,在 JSON_TABLE() 之前不允许使用 LATERAL,即使它是隐式的。

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

假设我们想要解决这个问题:给定一个销售团队中的人员表(其中每行描述一个销售团队的成员),以及一个包含所有销售的表(其中每行描述一笔销售:销售员、顾客、数量、日期),确定每个销售人员最大一笔销售额的大小和客户。这个问题可以用两种方法来解决。

解决问题的第一个方法:为每个销售人员计算最大销售额,并找到提供最大销售额的客户。在 MySQL 中,可以这样做:

1. SELECT
2.   salesperson.name,
3.   -- find maximum sale size for this salesperson
4.   (SELECT MAX(amount) AS amount
5.     FROM all_sales
6.     WHERE all_sales.salesperson_id = salesperson.id)
7.   AS amount,
8.   -- find customer for this maximum size
9.   (SELECT customer_name
10.     FROM all_sales
11.     WHERE all_sales.salesperson_id = salesperson.id
12.     AND all_sales.amount =
13.          -- find maximum size, again
14.          (SELECT MAX(amount) AS amount
15.            FROM all_sales
16.            WHERE all_sales.salesperson_id = salesperson.id))
17.   AS customer_name
18. FROM
19.   salesperson;

该查询效率低下,因为它为每个销售员计算最大额两次(第一个子查询一次,第二个子查询一次)。

我们可以计算每个销售员的最大销售额一次并将其“缓存”到派生表中,以获得效率增益,如下面修改的查询所示:

1. SELECT
2.   salesperson.name,
3.   max_sale.amount,
4.   max_sale_customer.customer_name
5. FROM
6.   salesperson,
7.   -- calculate maximum size, cache it in transient derived table max_sale
8.   (SELECT MAX(amount) AS amount
9.     FROM all_sales
10.    WHERE all_sales.salesperson_id = salesperson.id)
11.  AS max_sale,
12   -- find customer, reusing cached maximum size
13   (SELECT customer_name
14     FROM all_sales
15     WHERE all_sales.salesperson_id = salesperson.id
16     AND all_sales.amount =
17         -- the cached maximum size
18         max_sale.amount)
19   AS max_sale_customer;

但是,该查询在 SQL-92 中是非法的,因为派生表不能依赖于同一 FROM 子句中的其他表。派生表在查询期间必须是恒定的,不能包含对其他 FROM 子句表列的引用。如前所述,查询将产生以下错误:

1. ERROR 1054 (42S22): Unknown column ‘salesperson.id‘ in ‘where clause‘

在 SQL:1999 中,如果派生表前面加上 LATERAL 关键字(这意味着“此派生表依赖于其左侧的先前表”),则查询将合法:

1. SELECT
2   salesperson.name,
3   max_sale.amount,
4   max_sale_customer.customer_name
5 FROM
6   salesperson,
7   -- calculate maximum size, cache it in transient derived table mx_sale
8   LATERAL
9   (SELECT MAX(amount) AS amount
10     FROM all_sales
11     WHERE all_sales.salesperson_id = salesperson.id)
12   AS max_sale,
13   -- find customer, reusing cached maximum size
14   LATERAL
15   (SELECT customer_name
16     FROM all_sales
17     WHERE all_sales.salesperson_id = salesperson.id
18     AND all_sales.amount =
19         -- the cached maximum size
20        max_sale.amount)
21  AS max_sale_customer;

横向派生表不必是常量,并且每次顶部查询处理它所依赖的上表中的新行时,它都会被更新。

解决问题的第二种方法:如果 SELECT 列表中的子查询能返回多个列,则可以使用不同的解决方案:

1. SELECT
2   salesperson.name,
3.  -- find maximum size and customer at same time
4   (SELECT amount, customer_name
5     FROM all_sales
6     WHERE all_sales.salesperson_id = salesperson.id
7     ORDER BY amount DESC LIMIT 1)
8 FROM
9   salesperson;

这是高效率但非法的。它不起作用,因为这样的子查询只能返回一列:

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

可以尝试重写查询,从派生表中选择多个列:

1. SELECT
2.  salesperson.name,
3.  max_sale.amount,
4.  max_sale.customer_name
5.FROM
6.  salesperson,
7.  -- find maximum size and customer at same time
8.  (SELECT amount, customer_name
9.    FROM all_sales
10.    WHERE all_sales.salesperson_id = salesperson.id
11.   ORDER BY amount DESC LIMIT 1)
12.  AS max_sale;

然而,这也行不通。派生表依赖于 salesperson 表,因此如果没有 LATERAL 关键字会失败:

1. ERROR 1054 (42S22): Unknown column ‘salesperson.id‘ in ‘where clause‘

添加 LATERAL 关键字使查询合法:

1. SELECT
2.  salesperson.name,
3.  max_sale.amount,
4.  max_sale.customer_name
5.FROM
6.  salesperson,
7. -- find maximum size and customer at same time
8.LATERAL
9.  (SELECT amount, customer_name
10    FROM all_sales
11.    WHERE all_sales.salesperson_id = salesperson.id
12.    ORDER BY amount DESC LIMIT 1)
13. AS max_sale;

简言之,LATERAL 是解决上述两种方法中所有缺点的有效方法。

官方网址:
https://dev.mysql.com/doc/refman/8.0/en/lateral-derived-tables.html

MySQL的SQL语句 - 数据操作语句(13)- 子查询(10)

上一篇:js中的垃圾回收


下一篇:各大网站的字体样式