Postgresql CTE解析

一、简介

WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE（Common Table Expressions），它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。

二、CTE

CTE一般用于简化复杂join和子查询。WITH中SELECT的基本价值是将复杂的查询分解称为简单的部分

 三、WITH RECURSIVE（WITH 递归）

WITH语句还可以通过增加RECURSIVE修饰符来引入它自己，从而实现递归。在 WITH 子句中可以使用自身输出的数据。公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

WITH RECURSIVE实例：

 

 从上面的例子可以看出，WITH RECURSIVE语句包含了两个部分 non-recursive term（非递归部分），即上例中的union all前面部分 recursive term（递归部分），即上例中union all后面部分

执行步骤如下 ：

1、执行non-recursive term。（如果使用的是union而非union all，则需对结果去重）其结果作为recursive term中对result的引用，同时将这部分结果放入临时的working table中

2、重复执行如下步骤，直到working table为空：用working table的内容替换递归的自引用，执行recursive term，（如果使用union而非union all，去除重复数据），并用该结果（如果使用union而非union all，则是去重后的结果）替换working table 以上面的query为例，来看看具体过程。

以上面的query为例，来看看具体过程：

 

 四、示例

1、创建 COMPANY 表，数据内容如下

 2、下面将使用 WITH 子句在上表中查询数据：

 3、接下来让我们使用 RECURSIVE 关键字和 WITH 子句编写一个查询，查找 SALARY(工资) 字段小于 20000 的数据并计算它们的和：

 4、下面我们建立一张和 COMPANY 表相似的 COMPANY1 表，使用 DELETE 语句和 WITH 子句删除 COMPANY 表中 SALARY(工资) 字段大于等于 30000 的数据，并将删除的数据插入 COMPANY1 表，实现将 COMPANY 表数据转移到 COMPANY1 表中：

 5、CAMPANY 表和 CAMPANY1 表的数据如下：

 五、总结

1、可以使用递归 WITH RECURSIVE，从而实现其它方式无法实现或者不容易实现的查询

2、当不需要将查询结果被其它独立查询共享时，它比视图更灵活也更轻量

3、CTE只会被计算一次，且可在主查询中多次使用

4、CTE可极大提高代码可读性及可维护性

5、CTE不支持将主查询中where后的限制条件push down到CTE中，而普通的子查询支持