编程

Sql-WITH公用表表达式(common Table Expression)

文章目录

with as用于创建存在于内存中的临时表；将复杂的查询拆分为多个查询。

分为递归和非递归的with as 查询；递归是把自己的输出作为输入进行处理。

  • 非递归with
  • 递归with

递归with分为三个部分：1）a non-recursive term 2） UNION (or UNION ALL) 3） a recursive term。只有3）能够包含对查询输出的引用。

这里递归似乎不太合适，迭代更合适？

1）是循环的初始行 ；3)是循环体；2) 用于union每一行，结合为最终的输出。

下面是一个case：

img

上面这个语句的运行逻辑

  1. 首先递归(/此处用迭代更合适？)生成每一行数据；
  2. 每一行数据又作为生成下一行数据的输入
  3. 将每一行数据UNION为一个大表作为最终结果返回给cte_count。

pg支持with as ，后面mysql也支持

ref1: pg wbste,

ref2: cnblogs

https://www.essentialsql.com/recursive-ctes-explained/

ref2 中查看每个栏目的层数

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH COL_CTE(Id,Name,ParentId,tLevel )
AS
(
    --基本语句
    SELECT Id,Name,ParentId,0 AS tLevel FROM Col
    WHERE ParentId = 0
    UNION ALL
    --递归语句
    SELECT c.Id,c.Name,c.ParentId,ce.tLevel+1 AS tLevel FROM COL as c
    INNER JOIN COL_CTE AS ce 　　--递归调用
    ON c.ParentId = ce.Id
)
SELECT * FROM COL_CTE