当使用 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子查询。
总结
使用WITH查询可以大大简化复杂查询。它提供了一种方便的方法,可以将一个查询分成多个查询块,提高可读性和可维护性。在MySQL 8.0中,公共表达式支持非递归和递归查询,您可以使用它来处理大量数据。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:MySQL8.0的WITH查询详情 - Python技术站