MySQL WITH AS及递归查询

MySQL WITH AS及递归查询

WITH AS

官网：WITH 是 SQL 中的一个关键字，用于创建临时表达式（也称为 Common Table Expression，CTE），它允许你在一个查询中临时定义一个表达式，然后在后续的查询中引用它。

理解：当我们使用WITH AS 他会帮我们创建一个临时的表，这个临时表只在本次SQL中生效。当我们使用这个临时表二次以上时，他的效率是会比我们原本写两个一模一样的子查询是要搞的。

语法

WITH cte_name (column_name1, column_name2, ...) AS (
    -- CTE 查询
    SELECT ...
    FROM ...
    WHERE ...
)
-- 主查询
SELECT ...
FROM cte_name
WHERE ...

实例

WITH UserNoDisable as (
  -- 查询出用户表中disable为0的数据
	select * from User where disable = 0
)
select Stu.*,UserNoDisable.Extra from Stu Join UserNoDisable on Stu.Id = UserNoDisable.Id
union all
select Tea.*,UserNoDisable.Extra from Tea Join UserNoDisable on Stu.Id = UUserNoDisableser.Id;
-- 通过临时表可以使用我们的SQL，变得更简洁，原本需要写两次子查询的可以替换为去查临时表这也大大的提供了我们的性能。

注意：临时表一次可以建多个。

WITH tab1 as(...),tab2 as(...) select ....

递归运算（WITH RECURSIVE AS）

在临时表的基础上添加RECURSIVE 来达到递归运算。

with RECURSIVE  tab1(n) as (
    -- 这里是初始条件
    select 1
    -- 使用递归需要加上UNION ALL 关键字
    union all
    -- 这里是递归查询 按什么样的规则输出值，每一次递归都会将前一次的查询结果做为输入，直到查询的结果集为空时，停止递归
    select n+1 from tab1 where n < 10
)
select * from tab1;

结果
|n |
|1 | -- 1.初始条件结果
|2 | -- 2.拿初始条件的结果，按递归查询的规则执行一次
|3 | -- .... 那上一次的
|4 |
|5 |
|6 |
|7 |
|8 |
|9 |
|10| -- 查到这里后下一次查询结果为空了，则停止递归。

递归查询使用 WITH RECURSIVE 关键字结合递归公共表达式（RCTE）来实现。它的执行顺序可以分为以下几步：

1. 初始查询：
   • 首先执行递归查询的初始部分，也就是非递归的部分，这部分的结果作为递归查询的起点。
2. 递归查询：
   • 接着执行递归部分，这部分会基于初始查询的结果逐步地进行迭代。每次迭代会将前一次迭代的结果作为输入，然后应用递归规则。
3. 递归终止条件：
   • 在递归查询的过程中，必须定义一个终止条件（要不然就是死循环），当满足终止条件时，递归将停止。
4. 合并结果：
   • 将递归查询的结果与初始查询的结果进行合并，得到最终的查询结果。

总结来说，递归查询首先执行一个初始查询，然后基于初始查询的结果进行递归迭代，直到满足了设定的终止条件为止。最终，将递归查询的结果与初始查询的结果合并，得到最终的查询结果。

需要注意的是，在编写递归查询时，一定要谨慎地定义好递归规则和终止条件，以避免无限循环或者递归层级过深导致的性能问题。