SQL中的WITH AS语法
WITH AS先容SQL中的WITH AS语法是一种强大的工具，WITH AS可以简化复杂查询的编写，进步查询的可读性和维护性，WITH AS 语句允许用户定义一个临时的结果集，这个结果集被称为公共表表达式（Common Table Expression，简称CTE）。这个临时的结果集在查询执行期间存在，并且可以在同一个查询中被多次引用，就像一个临时表一样。这种语法的使用可以使得复杂的查询变得简朴，同时也可以进步查询效率。
WITH AS的长处
提升代码可读性：通过给复杂的子查询命名，WITH AS使得SQL代码的布局更加清晰，便于理解。特别是当查询涉及到多个条理的子查询时，每个CTE都可以看作是一个逻辑上的步骤，使得整个查询逻辑易于跟踪和理解。
减少代码重复：如果一个子查询必要在主查询中多次使用，使用WITH AS定义一次CTE就可以制止重复书写雷同的子查询代码，这不仅可以减少代码量，也低沉了出错的可能性。
优化查询性能：固然WITH AS本身并不直接提升查询性能，但在某些环境下数据库优化器能够更好地利用CTE进行查询优化。例如，如果一个CTE被多次引用，数据库可能只必要计算一次该CTE的结果，然后复用这个结果，而不是为每次引用都执行一遍子查询。
便于实现递归查询：WITH RECURSIVE是WITH AS的一个扩展，特别适用于处置惩罚层级布局或递归关系的数据，如组织布局、文件目录等。通过递归公用表表达式，可以方便地查询具有树形布局的数据，而无需编写复杂的自毗连查询。
模块化查询构建：CTE可以把一个复杂的查询分解成多个小的、可管理的部分，每个部分都可以单独测试和调试，这对于大型查询的开辟和维护尤其有效。
基本用法
//单个CTE
WITH 临时表名 AS (查询SQL)
SELECT * FROM 临时表名;
//多个CTE
WITH
临时表名1 AS (查询SQL),
临时表名2 AS (查询SQL)
SELECT * FROM 临时表名1 JOIN 临时表名2; CTE的定义和使用必须在同一个SQL内，因为它是一个临时表，只在查询期间存在，一个SQL也可以定义多个CTE。
具体使用示例：
//单个CTE
WITH cte AS (select 1 FROM dual)
SELECT * FROM cte;
//多个CTE
WITH
cte1 AS (SELECT 1 FROM dual),
cte2 AS (SELECT 2 FROM dual)
SELECT * FROM cte1 JOIN cte2; 定义CTE,并为每列重命名，列表中的名称数量必须与结果集中的列数相称。
WITH cte (col1, col2) AS (SELECT 1,2 from dual)
SELECT col1, col2 FROM cte; CTE嵌套
定义CTE也可以使用前面定义的CTE，如：
WITH
cte1 AS (SELECT 1 FROM dual),
cte2 AS (SELECT * FROM cte1)//使用cte1生成cte2
SELECT * FROM cte2; 递归查询
递归查询是CTE的一个重要特性，即CTE定义的可以使用本身，递归CTE的语法为：WITH RECURSIVE。
WITH RECURSIVE cte (n) AS
(
SELECT 1 FROM dual //没有使用cte，不参与递归
UNION ALL
SELECT n + 1 FROM cte WHERE n < 5//使用了cte，参与递归
)
SELECT * FROM cte;
//输出
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+ 定义递归CTE的SQL可以由多个部分组成，中间用UNION ALL或UNION毗连，没有使用cte的部分不参与递归，只执行一次，对于使用cte的部分递归执行，查询列n + 1此中的n使用的是上一次查询的结果，n < 5是递归制止的条件，这很重要，制止了无限循环。
下面是两个递归cte使用的例子：
WITH RECURSIVE cte AS
(
SELECT 1 AS n, 1 AS p, -1 AS q
UNION ALL
SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;
//输出
+------+------+------+
| n | p | q |
+------+------+------+
| 1 | 1 | -1 |
| 2 | -2 | 2 |
| 3 | 4 | -4 |
| 4 | -8 | 8 |
| 5 | 16 |-16 |
+------+------+------+
//斐波那契数列生成
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
SELECT 1, 0, 1
UNION ALL
SELECT n + 1, next_fib_n, fib_n + next_fib_n
FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;
//输出
+------+-------+------------+
| n | fib_n | next_fib_n |
+------+-------+------------+
| 1 | 0 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 2 |
| 4 | 2 | 3 |
| 5 | 3 | 5 |
| 6 | 5 | 8 |
| 7 | 8 | 13 |
| 8 | 13 | 21 |
| 9 | 21 | 34 |
| 10 | 34 | 55 |
+------+-------+------------+ 数据库支持
主流的数据库都支持with as语句，如myql、oracle、db2、hive、sql server、MariaDB、PostgreSQL等数据库，有些数据库只有较高版本才支持，如mysql8以上、sql server 2005以后的版本、Oracle 9i之后的版本。
总结
总之with as语句是一个强大好用的工具，合理的使用不仅能方便我们写出复杂的SQL语句，还能进步查询效率、查询的可读性和维护性。with as的先容就到这里，感谢阅读。
参考
MySQL 8.0 参考手册
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]