MySQL8.0的WITH查询详情

当使用 MySQL 8.0 时，我们可以使用 Common Table Expressions (CTE) 或者简称为 WITH 查询来简化查询语句。本文将向您介绍MySQL 8.0的 WITH 查询详情及其用法的完整攻略。

什么是WITH查询

WITH查询在MySQL8.0中被称为公共表达式。使用WITH语句，我们可以为一次查询创建一个临时表，并在查询中使用它。这个临时表可以由其他查询使用，因此可以大大简化复杂的查询。

一个公共表达式由以下部分组成：

WITH 子句定义公共表达式的名称和列。
声明子句定义公共子句的内容。
SELECT语句是执行实际查询的语句。

如何使用WITH查询

WITH查询的语法如下：

WITH cte_name [(column_name [, column_name] ...)] AS ( subquery ) SELECT statement;

其中cte_name是您给公共表达式命名的名称，column_name是您要包含在新创建的表中的列名，subquery是公共表达式的内容和SELECT语句是执行实际查询的语句。WITH查询支持递归和非递归、多个公共表达式和多个定义等特性。

非递归公共表达式

以下是一个用于举例的基本非递归公共表达式：

WITH sales AS (
    SELECT customer_id, SUM(amount) AS total_sales FROM orders 
    GROUP BY customer_id
)
SELECT customer_id, total_sales
FROM sales
WHERE total_sales > 100000;

这个查询使用了一个公共表达式，该公共表达式被命名为sales。在这个表达式中，我们从订单表中获取每个客户的总销售额。在使用了公共表达式后，我们可以很容易地使用WHERE条件语句筛选出我们需要的结果。

递归公共表达式

递归公共表达式是与自身递归关系的查询。在表的行之间建立关系时，这种公共表达式非常有用。

这是递归公共表达式的基本语法：

WITH recursive cte_name (column_name,column_name...) AS ( subquery_with_union )

其中recursive告诉MYSQL这是一个递归的公共表达式，cte_name是您给公共表达式命名的名称，subquery_with_union是公共表达式的内容和SELECT语句是执行实际查询的语句。

以下面的例子为例。它的功能是从指定的起始节点开始递归地遍历所有子节点，最终返回所有与起始节点相邻的子节点（直接相邻或间接相邻）。使用递归公共表达式可以简化查询。

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
           CAST(CONCAT('/',g.id,'/') AS CHAR(100)), false
        FROM graph g
       WHERE g.id = start_node_id
     UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
           CAST(CONCAT(sg.path,g.id,'/') AS CHAR(100)),
           g.id REGEXP sg.path as cycle
        FROM graph g, search_graph sg
       WHERE g.id = sg.link AND NOT cycle AND sg.depth < max_depth
)
SELECT * FROM search_graph;

在这个示例中，我们从graph表中选择了第1步。我们选择起始节点为start_node_id，然后创建一个公共表达式search_graph，用于追踪图表中的所有行。我们使用 UNION ALL 运算符将常规 SQL 查询与递归公共表达式组合在一起，并基于是否出现异常数据（即和起始节点直接或间接相邻的子节点是否存在环）以及遍历深度的限制来确定何时应停止递归。

公共表达式的输出也可以作为其他查询的输入。例如，下面的查询使用公共表达式来查找所有客户及其订购的产品数量。

WITH sales AS (
    SELECT customer_id, SUM(amount) AS total_sales FROM orders 
    GROUP BY customer_id
)
SELECT customers.customer_name, sales.total_sales, product_count
FROM customers 
JOIN sales ON customers.customer_id = sales.customer_id
JOIN (
    SELECT customer_id, COUNT(*) AS product_count FROM order_details 
    GROUP BY customer_id
) AS products ON customers.customer_id = products.customer_id;

在这个示例中，我们使用了两个公共表达式。一个是“sales”公共表达式，另一个是products子查询。