信用门户网站建设_大连虚拟主机_淘宝指数查询入口_sem优化托管

概述

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_nameWHERE 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, departmentFROM employeesWHERE age > 30
)
SELECT * FROM employees_over_30;

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

示例2

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

WITH month_list AS (SELECT 01 AS monthUNION SELECT 02UNION SELECT 03UNION SELECT 04UNION SELECT 05UNION SELECT 06UNION SELECT 07UNION SELECT 08UNION SELECT 09UNION SELECT 10UNION SELECT 11UNION SELECT 12)SELECTmonth_list.month AS createMonth,COALESCE(SUM(income), 0) AS total_valueFROMmonth_listLEFT JOINfinancialONcreate_month = month_list.monthGROUP BYmonth_list.monthORDER BYmonth_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_profitFROM sales_dataGROUP 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_rankFROM 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