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子查询。

总结

使用WITH查询可以大大简化复杂查询。它提供了一种方便的方法,可以将一个查询分成多个查询块,提高可读性和可维护性。在MySQL 8.0中,公共表达式支持非递归和递归查询,您可以使用它来处理大量数据。

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:MySQL8.0的WITH查询详情 - Python技术站

(0)
上一篇 2023年5月22日
下一篇 2023年5月22日

相关文章

  • mysql数据库存储过程数据迁移案例与比较

    cursor 与 insert …select 对比:     cursor:安全,不会造成死锁,可以在服务运行阶段跑,比较稳定。   insert…select :速度快,但是可能造成死锁,相比cursor能够成倍提升,在服务停止的情况下迁移,速度快 数据迁移案例:   首先数据的迁移绝对不是一朝一夕能够快速迁移完成的 ,如果可以很快完成的 dum…

    MySQL 2023年4月16日
    00
  • 基于java.lang.IllegalArgumentException异常报错问题及解决

    当我们在使用 Java 开发时,有时会遇到java.lang.IllegalArgumentException异常报错。这种异常通常是由于方法参数错误、格式错误或值域限制错误所致。为了解决这种异常报错问题,可以从以下几方面入手: 检查方法的参数是否合法 当我们调用方法时,有些方法需要传入参数。很多情况下,参数的合法性是有限制的,如果不满足条件就会导致Ille…

    database 2023年5月21日
    00
  • RDBMS和IBM DB2的区别

    RDBMS是关系型数据库管理系统的简称,它以关系数据模型为基础,使用表格来存储和管理数据。而IBM DB2是企业级的关系型数据库管理系统,是IBM公司开发的一种高性能、可扩展的数据库软件。 以下是RDBMS和IBM DB2的区别及实例说明: RDBMS 数据组织结构 RDBMS使用表格来存储和管理数据。表格由行和列组成,每个表格包含多个字段或属性,每个字段或…

    database 2023年3月27日
    00
  • 数据库常用的sql语句汇总

    数据库是存储数据的大型软件系统,而SQL是可用于访问和管理数据库的语言。因此,掌握SQL语言是数据库开发中非常重要的一环。在本文中,我们将分享一个“数据库常用的SQL语句汇总”攻略,帮助数据库开发者更好地理解SQL语句以及它们在实际工作中的应用。 SQL语句的类型 SQL语句可以分为以下几种类型: DDL(Data Definition Language):…

    database 2023年5月21日
    00
  • 如何使用Python在MySQL中使用限制查询?

    在MySQL中,可以使用LIMIT子句对查询结果进行限制。在Python中,可以使用MySQL连接来执行限制查询。以下是在Python中使用限制查询的完整攻略,包括限制查询的基本语法、使用限制查询的示例以如在Python中使用限制查询。 限制查询的基本语法 限制查询的基本语法如下: column_name) FROM table_name LIMIT num…

    python 2023年5月12日
    00
  • Teradata和Pig的区别

    Teradata和Pig都是用于大数据处理的工具,但它们的设计思路和使用方式有很大的不同。接下来我将详细讲解它们的区别,并且给出一些实例来说明。 Teradata Teradata是一个用于存储和处理大数据的关系型数据库管理系统。它使用的是MPP(Massively Parallel Processing,大规模并行处理)的架构,可以实现高速的数据查询和处理…

    database 2023年3月27日
    00
  • sql server代理中作业执行SSIS包失败的解决办法

    针对“sql server代理中作业执行SSIS包失败”的问题,我们可以按照以下步骤解决: 1. 确认项目及包的可靠性 在执行前,我们需要先确认项目及包的可靠性,可以通过以下方式进行验证: 1.1 在BIDS(SSDT)中测试SSIS包 我们可以在BIDS(或SSDT)中测试SSIS包,在环境较为稳定的情况下可以正常运行,那么我们需要询问自己下面的问题: 1…

    database 2023年5月21日
    00
  • 一文带你吃透Redis

    目录 1. 基本数据结构 2. 数据持久化 3. 高可用 4. 缓存 文章字数大约1.9万字,阅读大概需要66分钟,建议收藏后慢慢阅读!!! 1. 基本数据结构 什么是Redis Redis是一个数据库,不过与传统数据库不同的是Redis的数据库是存在内存中,所以读写速度非常快,因此 Redis被广泛应用于缓存方向。 除此之外,Redis也经常用来做分布式锁…

    Redis 2023年4月10日
    00
合作推广
合作推广
分享本页
返回顶部