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可以把一个复杂的查询分解成多个小的、可管理的部分，每个部分都可以单独测试和调试，这对于大型查询的开辟和维护尤其有效。
基本用法

1. //单个CTE
2. WITH 临时表名 AS (查询SQL)
3. SELECT * FROM 临时表名;
5. //多个CTE
6. WITH
7.   临时表名1 AS (查询SQL),
8.   临时表名2 AS (查询SQL)
9. SELECT * FROM 临时表名1 JOIN 临时表名2;

复制代码

CTE的定义和使用必须在同一个SQL内，因为它是一个临时表，只在查询期间存在，一个SQL也可以定义多个CTE。
具体使用示例：

1. //单个CTE
2. WITH cte AS (select 1 FROM dual)
3. SELECT * FROM cte;
5. //多个CTE
6. WITH
7.   cte1 AS (SELECT 1 FROM dual),
8.   cte2 AS (SELECT 2 FROM dual)
9. SELECT * FROM cte1 JOIN cte2;

复制代码

定义CTE,并为每列重命名，列表中的名称数量必须与结果集中的列数相称。

1. WITH cte (col1, col2) AS (SELECT 1,2 from dual)
2. SELECT col1, col2 FROM cte;

复制代码

CTE嵌套

定义CTE也可以使用前面定义的CTE，如：

1. WITH
2.   cte1 AS (SELECT 1 FROM dual),
3.   cte2 AS (SELECT * FROM cte1)//使用cte1生成cte2
4. SELECT * FROM cte2;

复制代码

递归查询

递归查询是CTE的一个重要特性，即CTE定义的可以使用本身，递归CTE的语法为：WITH RECURSIVE。

1. WITH RECURSIVE cte (n) AS
2. (
3.   SELECT 1 FROM dual //没有使用cte，不参与递归
4.   UNION ALL
5.   SELECT n + 1 FROM cte WHERE n < 5//使用了cte，参与递归
6. )
7. SELECT * FROM cte;
8. //输出
9. +------+
10. | n    |
11. +------+
12. |    1 |
13. |    2 |
14. |    3 |
15. |    4 |
16. |    5 |
17. +------+

复制代码

定义递归CTE的SQL可以由多个部分组成，中间用UNION ALL或UNION毗连，没有使用cte的部分不参与递归，只执行一次，对于使用cte的部分递归执行，查询列n + 1此中的n使用的是上一次查询的结果，n < 5是递归制止的条件，这很重要，制止了无限循环。
下面是两个递归cte使用的例子：

1. WITH RECURSIVE cte AS
2. (
3.   SELECT 1 AS n, 1 AS p, -1 AS q
4.   UNION ALL
5.   SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
6. )
7. SELECT * FROM cte;
8. //输出
9. +------+------+------+
10. | n    | p    | q    |
11. +------+------+------+
12. |    1 |    1 |   -1 |
13. |    2 |   -2 |    2 |
14. |    3 |    4 |   -4 |
15. |    4 |   -8 |    8 |
16. |    5 |   16 |  -16 |
17. +------+------+------+
19. //斐波那契数列生成
20. WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
21. (
22.   SELECT 1, 0, 1
23.   UNION ALL
24.   SELECT n + 1, next_fib_n, fib_n + next_fib_n
25.     FROM fibonacci WHERE n < 10
26. )
27. SELECT * FROM fibonacci;
28. //输出
29. +------+-------+------------+
30. | n    | fib_n | next_fib_n |
31. +------+-------+------------+
32. |    1 |     0 |          1 |
33. |    2 |     1 |          1 |
34. |    3 |     1 |          2 |
35. |    4 |     2 |          3 |
36. |    5 |     3 |          5 |
37. |    6 |     5 |          8 |
38. |    7 |     8 |         13 |
39. |    8 |    13 |         21 |
40. |    9 |    21 |         34 |
41. |   10 |    34 |         55 |
42. +------+-------+------------+

复制代码

数据库支持

主流的数据库都支持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企服之家，中国第一个企服评测及商务社交产业平台。