MySQL表达式之公用表表达式(CTE)

概述

WITH AS短语，也叫做子查询部分（subquery factoring），是用来定义一个SQL片断，该SQL片断会被整个SQL语句所用到。这个语句算是公用表表达式（CTE，Common Table Expression）。

WITH AS 语法是MySQL中的一种临时结果集，CTE可以在select , insert , update , delete , merge语句的执行范围定义。通过使用WITH AS语句，可以将一个查询的结果存储在一个临时表中，然后在后续的查询中引用这个临时表。这样可以简化复杂的查询，提高代码的可读性和可维护性。

官网链接：https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive

with-as 意义：
1、对于多次反复出现的子查询，可以降低扫描表的次数和减少代码重写，优化性能和使编码更加简洁，也可以在UNION ALL的不同部分，作为提供数据的部分。
2、对于UNION ALL，使用WITH AS定义了一个UNION ALL语句，当该片断被调用2次以上，优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。

版本支持：

• mysql版本：8以及8以上的
• sql server：sql server 2005以后的版本
• oracle：Oracle 9i的第二版本数据库

WITH AS语法的基本结构

WITH temporary_table_name (column1, column2, ...) AS (
  SELECT column1, column2, ...
  FROM table_name
  WHERE condition
)
SELECT * FROM temporary_table_name;

其中，temporary_table_name是临时表的名称，column1, column2, …是临时表的列名，table_name是要查询的表名，condition是筛选条件。

CTE的特点

• 临时性：CTE只在定义它们的查询中有效，查询结束后，CTE自动消失。
• 可递归：CTE可以定义递归查询，这对于处理层次结构数据非常有用。
• 可读性：CTE使得复杂的SQL查询更加清晰，易于理解和维护。
• 重用性：在同一个查询中，CTE可以被多次引用，避免了重复的子查询。
• 灵活性：CTE可以包含多个SELECT语句，并且可以嵌套使用。

CTE的使用场景

• 简化复杂查询：CTE可以将复杂的查询逻辑分解为多个简单的步骤，使得查询更加易于理解和维护。
• 递归查询：CTE非常适合处理需要递归的查询，例如组织结构、文件系统路径等。
• 避免子查询：CTE可以替代一些复杂的子查询，使SQL语句更加简洁。
• 性能优化：在某些情况下，CTE可以帮助优化查询性能，尤其是在递归查询中。

示例1

假设我们有一个员工表（employees），包含员工的姓名、年龄和部门信息。现在我们想要查询所有年龄大于30的员工，并按照部门进行分组。我们可以使用WITH AS语句来实现这个需求：

WITH employees_over_30 AS (
  SELECT name, age, department
  FROM employees
  WHERE age > 30
)
SELECT * FROM employees_over_30;

在这个例子中，我们首先创建了一个名为employees_over_30的临时表，将年龄大于30的员工的信息存储在其中。然后，我们在后续的查询中引用了这个临时表，按照部门进行了分组。这样可以使查询更加简洁，易于理解。

示例2

统计财务表中的每个月份的收入的总额

WITH month_list AS (
            SELECT 01 AS month
        UNION SELECT 02
              UNION SELECT 03
              UNION SELECT 04
              UNION SELECT 05
              UNION SELECT 06
              UNION SELECT 07
              UNION SELECT 08
              UNION SELECT 09
              UNION SELECT 10
              UNION SELECT 11
              UNION SELECT 12
                  )
        SELECT
            month_list.month AS createMonth,
            COALESCE(SUM(income), 0) AS total_value
        FROM
            month_list
                LEFT JOIN
            financial
            ON
                create_month = month_list.month
        GROUP BY
            month_list.month
        ORDER BY
            month_list.month

示例3

下面是一个比较复杂的例子，假设我们有一个销售数据表（sales_data），包含日期（date）、产品ID（product_id）、销售额（sales）和利润（profit）。现在我们需要计算每个产品的总销售额和总利润，并按照产品ID进行分组。我们可以使用WITH AS语句来实现这个需求：

WITH product_sales AS (
  SELECT product_id, SUM(sales) as total_sales, SUM(profit) as total_profit
  FROM sales_data
  GROUP BY product_id
),
product_sales_with_rank AS (
  SELECT product_id, total_sales, total_profit,
         RANK() OVER (ORDER BY total_sales DESC) as sales_rank,
         RANK() OVER (ORDER BY total_profit DESC) as profit_rank
  FROM product_sales
)
SELECT product_id, total_sales, total_profit,
       sales_rank, profit_rank
FROM product_sales_with_rank;

在这个例子中，我们首先使用WITH AS语句创建了一个名为product_sales的临时表，用于计算每个产品的总销售额和总利润。然后，我们创建了另一个名为product_sales_with_rank的临时表，用于计算每个产品的销售额排名和利润排名。最后，我们从product_sales_with_rank临时表中选择所需的列进行查询。

参考连接
https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html#common-table-expressions
https://blog.csdn.net/weixin_44823875/article/details/142913251
https://blog.csdn.net/weixin_49114503/article/details/131796381
https://blog.csdn.net/weixin_43194885/article/details/122199299
https://www.jb51.net/article/236061.htm
https://blog.csdn.net/m0_73549087/article/details/143746573
https://blog.csdn.net/weixin_72610956/article/details/135088655
https://www.php.cn/faq/613575.html