PG中的CTE（with语句、公共表表达式）

由小麦苗 · 2021年7月20日

0 656 8

👉 本文共约7506个字，系统预计阅读时间或需29分钟。

本页目录 隐藏

WITH RECURSIVE（WITH 递归）

简介

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

使用PostgreSQL CTE(common table expressions)可以简化复杂查询，PostgreSQL CTE(common table expressions) 是临时结果，可以在其他SQL中引用，如SELECT, INSERT, UPDATE 和 DELETE，其仅存在于查询执行期间。

在 PostgreSQL 中，WITH 子句提供了一种编写辅助语句的方法，以便在更大的查询中使用。

WITH 子句有助于将复杂的大型查询分解为更简单的表单，便于阅读。这些语句通常称为通用表表达式（Common Table Express， CTE），也可以当做一个为查询而存在的临时表。

WITH 子句是在多次执行子查询时特别有用，允许我们在查询中通过它的名称(可能是多次)引用它。

WITH 子句在使用前必须先定义。

下面是创建CTE的语法：

WITH cte_name (column_list) AS (
    CTE_query_definition 
)
statement;

WITH cte_name (column_list) AS (

CTE_query_definition

)

statement;

首先，指定CTE的名称，接着是可选的列字段列表
其次，在with子句体内，指定查询作为返回结果集，如果没有显示指定列字段列表，则CTE_query_definition 的select 字段列表将作为CTE的字段列表。
第三，在其他SQL语句中可以向使用表或视图一样使用CTE，语句包括SELECT, INSERT, UPDATE 和 DELETE。
CTE一般用于简化复杂join和子查询。

WITH中SELECT的基本价值是将复杂的查询分解称为简单的部分。一个例子：

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

WITH regional_sales AS (

SELECT region, SUM(amount) AS total_sales

FROM orders

GROUP BY region

), top_regions AS (

SELECT region

FROM regional_sales

WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)

)

SELECT region,

product,

SUM(quantity) AS product_units,

SUM(amount) AS product_sales

FROM orders

WHERE region IN (SELECT region FROM top_regions)

GROUP BY region, product;

它只显示在高销售区域每种产品的销售总额。WITH子句定义了两个辅助语句regional_sales和top_regions，其中regional_sales的输出用在top_regions中而top_regions的输出用在主SELECT查询。这个例子可以不用WITH来书写，但是我们必须要用两层嵌套的子SELECT。使用这种方法要更简单些。

可选的RECURSIVE修饰符将WITH从单纯的句法便利变成了一种在标准SQL中不能完成的特性。通过使用RECURSIVE，一个WITH查询可以引用它自己的输出。一个非常简单的例子是计算从1到100的整数合的查询：

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;

WITH RECURSIVE t(n) AS (

VALUES (1)

UNION ALL

SELECT n+1 FROM t WHERE n < 100

)

SELECT sum(n) FROM t;

一个递归WITH查询的通常形式总是一个非递归项，然后是UNION（或者UNION ALL），再然后是一个递归项，其中只有递归项能够包含对于查询自身输出的引用。这样一个查询可以被这样执行：

递归查询求值

计算非递归项。对UNION（但不对UNION ALL），抛弃重复行。把所有剩余的行包括在递归查询的结果中，并且也把它们放在一个临时的工作表中。
只要工作表不为空，重复下列步骤：
1. 计算递归项，用当前工作表的内容替换递归自引用。对UNION（不是UNION ALL），抛弃重复行以及那些与之前结果行重复的行。将剩下的所有行包括在递归查询的结果中，并且也把它们放在一个临时的中间表中。
2. 用中间表的内容替换工作表的内容，然后清空中间表。

注意

严格来说，这个处理是迭代而不是递归，但是RECURSIVE是SQL标准委员会选择的术语。

在上面的例子中，工作表在每一步只有一个行，并且它在连续的步骤中取值从1到100。在第100步，由于WHERE子句导致没有输出，因此查询终止。

递归查询通常用于处理层次或者树状结构的数据。一个有用的例子是这个用于找到一个产品的直接或间接部件的查询，只要给定一个显示了直接包含关系的表：

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part, p.quantity
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part

WITH RECURSIVE included_parts(sub_part, part, quantity) AS (

SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'

UNION ALL

SELECT p.sub_part, p.part, p.quantity

FROM included_parts pr, parts p

WHERE p.part = pr.sub_part

)

SELECT sub_part, SUM(quantity) as total_quantity

FROM included_parts

GROUP BY sub_part

在使用递归查询时，确保查询的递归部分最终将不返回元组非常重要，否则查询将会无限循环。在某些时候，使用UNION替代UNION ALL可以通过抛弃与之前输出行重复的行来达到这个目的。不过，经常有循环不涉及到完全重复的输出行：它可能只需要检查一个或几个域来看相同点之前是否达到过。处理这种情况的标准方法是计算一个已经访问过值的数组。例如，考虑下面这个使用link域搜索表graph的查询：

WITH RECURSIVE search_graph(id, link, data, depth) AS (
    SELECT g.id, g.link, g.data, 1
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1
    FROM graph g, search_graph sg
    WHERE g.id = sg.link
)
SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth) AS (

SELECT g.id, g.link, g.data, 1

FROM graph g

UNION ALL

SELECT g.id, g.link, g.data, sg.depth + 1

FROM graph g, search_graph sg

WHERE g.id = sg.link

)

SELECT * FROM search_graph;

如果link关系包含环，这个查询将会循环。因为我们要求一个“depth”输出，仅仅将UNION ALL 改为UNION不会消除循环。反过来在我们顺着一个特定链接路径搜索时，我们需要识别我们是否再次到达了一个相同的行。我们可以项这个有循环倾向的查询增加两个列path和cycle：

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
      ARRAY[g.id],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || g.id,
      g.id = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (

SELECT g.id, g.link, g.data, 1,

ARRAY[g.id],

false

FROM graph g

UNION ALL

SELECT g.id, g.link, g.data, sg.depth + 1,

path || g.id,

g.id = ANY(path)

FROM graph g, search_graph sg

WHERE g.id = sg.link AND NOT cycle

)

SELECT * FROM search_graph;

除了阻止环，数组值对于它们自己的工作显示到达任何特定行的“path”也有用。

在通常情况下如果需要检查多于一个域来识别一个环，请用行数组。例如，如果我们需要比较域f1和f2：

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (
    SELECT g.id, g.link, g.data, 1,
      ARRAY[ROW(g.f1, g.f2)],
      false
    FROM graph g
  UNION ALL
    SELECT g.id, g.link, g.data, sg.depth + 1,
      path || ROW(g.f1, g.f2),
      ROW(g.f1, g.f2) = ANY(path)
    FROM graph g, search_graph sg
    WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;

WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (

SELECT g.id, g.link, g.data, 1,

ARRAY[ROW(g.f1, g.f2)],

false

FROM graph g

UNION ALL

SELECT g.id, g.link, g.data, sg.depth + 1,

path || ROW(g.f1, g.f2),

ROW(g.f1, g.f2) = ANY(path)

FROM graph g, search_graph sg

WHERE g.id = sg.link AND NOT cycle

)

SELECT * FROM search_graph;

提示

在通常情况下只有一个域需要被检查来识别一个环，可以省略ROW()语法。这允许使用一个简单的数组而不是一个组合类型数组，可以获得效率。

提示

递归查询计算算法使用宽度优先搜索顺序产生它的输出。你可以通过让外部查询ORDER BY一个以这种方法构建的“path”，用来以深度优先搜索顺序显示结果。

当你不确定查询是否可能循环时，一个测试查询的有用技巧是在父查询中放一个LIMIT。例如，这个查询没有LIMIT时会永远循环：

WITH RECURSIVE t(n) AS (
    SELECT 1
  UNION ALL
    SELECT n+1 FROM t
)
SELECT n FROM t LIMIT 100;

WITH RECURSIVE t(n) AS (

SELECT 1

UNION ALL

SELECT n+1 FROM t

)

SELECT n FROM t LIMIT 100;

这会起作用，因为PostgreSQL的实现只计算WITH查询中被父查询实际取到的行。不推荐在生产中使用这个技巧，因为其他系统可能以不同方式工作。同样，如果你让外层查询排序递归查询的结果或者把它们连接成某种其他表，这个技巧将不会起作用，因为在这些情况下外层查询通常将尝试取得WITH查询的所有输出。

WITH查询的一个有用的特性是在每一次父查询的执行中它们通常只被计算一次，即使它们被父查询或兄弟WITH查询引用了超过一次。因此，在多个地方需要的昂贵计算可以被放在一个WITH查询中来避免冗余工作。另一种可能的应用是阻止不希望的多个函数计算产生副作用。但是，从另一方面来看，优化器不能将来自父查询的约束下推到乘法引用WITH查询，因为当他应该只影响一个时它可能会影响所有使用WITH查询的输出的使用。乘法引用WITH查询通常将会被按照所写的方式计算，而不抑制父查询以后可能会抛弃的行（但是，如上所述，如果对查询的引用只请求有限数目的行，计算可能会提前停止）。

但是，如果 WITH 查询是非递归和边际效应无关的（就是说，它是一个SELECT包含没有可变函数），则它可以合并到父查询中，允许两个查询级别的联合优化。默认情况下，这发生在如果父查询仅引用 WITH查询一次的时候，而不是它引用WITH查询多于一次时。你可以超越控制这个决策，通过指定 MATERIALIZED 来强制分开计算 WITH 查询，或者通过指定 NOT MATERIALIZED来强制它被合并到父查询中。后一种选择存在重复计算WITH查询的风险，但它仍然能提供净节省，如果WITH查询的每个使用只需要WITH查询的完整输出的一小部分。

这些规则的一个简单示例是

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w WHERE key = 123;

WITH w AS (

SELECT * FROM big_table

)

SELECT * FROM w WHERE key = 123;

这个 WITH 查询将被合并，生成相同的执行计划为

SELECT * FROM big_table WHERE key = 123;

1	SELECT * FROM big_table WHERE key = 123;

特别是，如果在key上有一个索引，它可能只用于获取具有 key = 123的行。另一方面，在

WITH w AS (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

WITH w AS (

SELECT * FROM big_table

)

SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref

WHERE w2.key = 123;

WITH查询将被物化，生成一个big_table的临时拷贝，然后与其自身 — 联合，这样将不能从索引上获得任何好处。如果写成下面的形式，这个查询将被执行得更有效率。

WITH w AS NOT MATERIALIZED (
    SELECT * FROM big_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref
WHERE w2.key = 123;

WITH w AS NOT MATERIALIZED (

SELECT * FROM big_table

)

SELECT * FROM w AS w1 JOIN w AS w2 ON w1.key = w2.ref

WHERE w2.key = 123;

所以父查询的限制可以直接应用于big_table的扫描。

一个NOT MATERIALIZED 可能不理想的例子为

WITH w AS (
    SELECT key, very_expensive_function(val) as f FROM some_table
)
SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

WITH w AS (

SELECT key, very_expensive_function(val) as f FROM some_table

)

SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;

在这里，WITH查询的物化确保very_expensive_function每个表行只计算一次，而不是两次。

以上的例子只展示了和SELECT一起使用的WITH，但是它可以被以相同的方式附加在INSERT、UPDATE或DELETE上。在每一种情况中，它实际上提供了可在主命令中引用的临时表。

WITH中的数据修改语句

你可以在WITH中使用数据修改语句（INSERT、UPDATE或DELETE）。这允许你在同一个查询中执行多个而不同操作。一个例子：

WITH moved_rows AS (
    DELETE FROM products
    WHERE
        "date" >= '2010-10-01' AND
        "date" < '2010-11-01'
    RETURNING *
)
INSERT INTO products_log
SELECT * FROM moved_rows;

WITH moved_rows AS (

DELETE FROM products

WHERE

"date" >= '2010-10-01' AND

"date" < '2010-11-01'

RETURNING *

)

INSERT INTO products_log

SELECT * FROM moved_rows;

这个查询实际上从products把行移动到products_log。WITH中的DELETE删除来自products的指定行，以它的RETURNING子句返回它们的内容，并且接着主查询读该输出并将它插入到products_log。

上述例子中好的一点是WITH子句被附加给INSERT，而没有附加给INSERT的子SELECT。这是必需的，因为数据修改语句只允许出现在附加给顶层语句的WITH子句中。不过，普通WITH可见性规则应用，这样才可能从子SELECT中引用到WITH语句的输出。

正如上述例子所示，WITH中的数据修改语句通常具有RETURNING子句（见第 6.4 节）。它是RETURNING子句的输出，不是\数据修改语句的目标表，它形成了剩余查询可以引用的临时表。如果一个WITH中的数据修改语句缺少一个RETURNING子句，则它形不成临时表并且不能在剩余的查询中被引用。但是这样一个语句将被执行。一个非特殊使用的例子：

WITH t AS (
    DELETE FROM foo
)
DELETE FROM bar;

WITH t AS (

DELETE FROM foo

)

DELETE FROM bar;

这个例子将从表foo和bar中移除所有行。被报告给客户端的受影响行的数目可能只包括从bar中移除的行。

数据修改语句中不允许递归自引用。在某些情况中可以采取引用一个递归WITH的输出来操作这个限制，例如：

WITH RECURSIVE included_parts(sub_part, part) AS (
    SELECT sub_part, part FROM parts WHERE part = 'our_product'
  UNION ALL
    SELECT p.sub_part, p.part
    FROM included_parts pr, parts p
    WHERE p.part = pr.sub_part
)
DELETE FROM parts
  WHERE part IN (SELECT part FROM included_parts);

WITH RECURSIVE included_parts(sub_part, part) AS (

SELECT sub_part, part FROM parts WHERE part = 'our_product'

UNION ALL

SELECT p.sub_part, p.part

FROM included_parts pr, parts p

WHERE p.part = pr.sub_part

)

DELETE FROM parts

WHERE part IN (SELECT part FROM included_parts);

这个查询将会移除一个产品的所有直接或间接子部件。

WITH中的数据修改语句只被执行一次，并且总是能结束，而不管主查询是否读取它们所有（或者任何）的输出。注意这和WITH中SELECT的规则不同：正如前一小节所述，直到主查询要求SELECT的输出时，SELECT才会被执行。

The sub-statements in WITH中的子语句被和每一个其他子语句以及主查询并发执行。因此在使用WITH中的数据修改语句时，指定更新的顺序实际是以不可预测的方式发生的。所有的语句都使用同一个snapshot执行（参见第 13 章），因此它们不能“看见”在目标表上另一个执行的效果。这减轻了行更新的实际顺序的不可预见性的影响，并且意味着RETURNING数据是在不同WITH子语句和主查询之间传达改变的唯一方法。其例子

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;

WITH t AS (

UPDATE products SET price = price * 1.05

RETURNING *

)

SELECT * FROM products;

外层SELECT可以返回在UPDATE动作之前的原始价格，而在

WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;

WITH t AS (

UPDATE products SET price = price * 1.05

RETURNING *

)

SELECT * FROM t;

外部SELECT将返回更新过的数据。

在一个语句中试图两次更新同一行是不被支持的。只会发生一次修改，但是该办法不能很容易地（有时是不可能）可靠地预测哪一个会被执行。这也应用于删除一个已经在同一个语句中被更新过的行：只有更新被执行。因此你通常应该避免尝试在一个语句中尝试两次修改同一个行。尤其是防止书写可能影响被主语句或兄弟子语句修改的相同行。这样一个语句的效果将是不可预测的。

当前，在WITH中一个数据修改语句中被用作目标的任何表不能有条件规则、ALSO规则或INSTEAD规则，这些规则会扩展成为多个语句。

WITH RECURSIVE（WITH 递归）

WITH语句还可以通过增加RECURSIVE修饰符来引入它自己，从而实现递归。

在 WITH 子句中可以使用自身输出的数据。

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。

WITH RECURSIVE实例

WITH RECURSIVE一般用于处理逻辑上层次化或树状结构的数据，典型的使用场景是寻找直接及间接子结点。

定义下面这样的表，存储每个区域（省、市、区）的id，名字及上级区域的id

CREATE TABLE chinamap
(
  id INTEGER,
  pid INTEGER,
  name TEXT
);

CREATE TABLE chinamap

(

id INTEGER,

pid INTEGER,

name TEXT

);

需要查出某个省，比如湖北省，管辖的所有市及市辖地区，可以通过WITH RECURSIVE来实现，如下

WITH RECURSIVE result AS
(
  SELECCT
    id,
    name
  FROM  chinamap
  WHERE id = 11
  UNION ALL
  SELECT
    origin.id,
    result.name || ' > ' || origin.name
  FROM result
  JOIN chinamap origin
  ON origin.pid = result.id
)
SELECT
  id,
  name
FROM result;

WITH RECURSIVE result AS

(

SELECCT

id,

name

FROM chinamap

WHERE id = 11

UNION ALL

SELECT

origin.id,

result.name || ' > ' || origin.name

FROM result

JOIN chinamap origin

ON origin.pid = result.id

)

SELECT

id,

name

FROM result;

结果如下

 id  |           name           
-----+--------------------------
  11 | 湖北省
 110 | 湖北省 > 武汉市
 120 | 湖北省 > 孝感市
 130 | 湖北省 > 宜昌市
 140 | 湖北省 > 随州市
 150 | 湖北省 > 仙桃市
 160 | 湖北省 > 荆门市
 170 | 湖北省 > 枝江市
 180 | 湖北省 > 神农架市
 111 | 湖北省 > 武汉市 > 武昌区
 112 | 湖北省 > 武汉市 > 下城区
 113 | 湖北省 > 武汉市 > 江岸区
 114 | 湖北省 > 武汉市 > 江汉区
 115 | 湖北省 > 武汉市 > 汉阳区
 116 | 湖北省 > 武汉市 > 洪山区
 117 | 湖北省 > 武汉市 > 青山区
(16 rows)

id | name

-----+--------------------------

11 | 湖北省

110 | 湖北省 > 武汉市

120 | 湖北省 > 孝感市

130 | 湖北省 > 宜昌市

140 | 湖北省 > 随州市

150 | 湖北省 > 仙桃市

160 | 湖北省 > 荆门市

170 | 湖北省 > 枝江市

180 | 湖北省 > 神农架市

111 | 湖北省 > 武汉市 > 武昌区

112 | 湖北省 > 武汉市 > 下城区

113 | 湖北省 > 武汉市 > 江岸区

114 | 湖北省 > 武汉市 > 江汉区

115 | 湖北省 > 武汉市 > 汉阳区

116 | 湖北省 > 武汉市 > 洪山区

117 | 湖北省 > 武汉市 > 青山区

(16 rows)

WITH RECURSIVE 执行过程

从上面的例子可以看出，WITH RECURSIVE语句包含了两个部分

non-recursive term（非递归部分），即上例中的union all前面部分
recursive term（递归部分），即上例中union all后面部分

执行步骤如下

执行non-recursive term。（如果使用的是union而非union all，则需对结果去重）其结果作为recursive term中对result的引用，同时将这部分结果放入临时的working table中
重复执行如下步骤，直到working table为空：用working table的内容替换递归的自引用，执行recursive term，（如果使用union而非union all，去除重复数据），并用该结果（如果使用union而非union all，则是去重后的结果）替换working table

以上面的query为例，来看看具体过程
1.执行

SELECT
  id,
  name
FROM chinamap
WHERE id = 11

SELECT

id,

name

FROM chinamap

WHERE id = 11

结果集和working table为

11 | 湖北

11 | 湖北

2.执行

SELECT
  origin.id,
  result.name || ' > ' || origin.name
FROM result
JOIN chinamap origin
ON origin.pid = result.id

SELECT

origin.id,

result.name || ' > ' || origin.name

FROM result

JOIN chinamap origin

ON origin.pid = result.id

结果集和working table为

110 | 湖北省 > 武汉市
120 | 湖北省 > 孝感市
130 | 湖北省 > 宜昌市
140 | 湖北省 > 随州市
150 | 湖北省 > 仙桃市
160 | 湖北省 > 荆门市
170 | 湖北省 > 枝江市
180 | 湖北省 > 神农架市

110 | 湖北省 > 武汉市

120 | 湖北省 > 孝感市

130 | 湖北省 > 宜昌市

140 | 湖北省 > 随州市

150 | 湖北省 > 仙桃市

160 | 湖北省 > 荆门市

170 | 湖北省 > 枝江市

180 | 湖北省 > 神农架市

3.再次执行recursive query，结果集和working table为

111 | 湖北省 > 武汉市 > 武昌区
112 | 湖北省 > 武汉市 > 下城区
113 | 湖北省 > 武汉市 > 江岸区
114 | 湖北省 > 武汉市 > 江汉区
115 | 湖北省 > 武汉市 > 汉阳区
116 | 湖北省 > 武汉市 > 洪山区
117 | 湖北省 > 武汉市 > 青山区

111 | 湖北省 > 武汉市 > 武昌区

112 | 湖北省 > 武汉市 > 下城区

113 | 湖北省 > 武汉市 > 江岸区

114 | 湖北省 > 武汉市 > 江汉区

115 | 湖北省 > 武汉市 > 汉阳区

116 | 湖北省 > 武汉市 > 洪山区

117 | 湖北省 > 武汉市 > 青山区

4.继续执行recursive query，结果集和working table为空
5.结束递归，将前三个步骤的结果集合并，即得到最终的WITH RECURSIVE的结果集

严格来讲，这个过程实现上是一个迭代的过程而非递归，不过RECURSIVE这个关键词是SQL标准委员会定立的，所以PostgreSQL也延用了RECURSIVE这一关键词。

WITH RECURSIVE 防止死循环

从上一节中可以看到，决定是否继续迭代的working table是否为空，如果它永不为空，则该CTE将陷入无限循环中。
对于本身并不会形成循环引用的数据集，无段作特别处理。而对于本身可能形成循环引用的数据集，则须通过SQL处理。

一种方式是使用UNION而非UNION ALL，从而每次recursive term的计算结果都会将已经存在的数据清除后再存入working table，使得working table最终会为空，从而结束迭代。

然而，这种方法并不总是有效的，因为有时可能需要这些重复数据。同时UNION只能去除那些所有字段都完全一样的记录，而很有可能特定字段集相同的记录即应该被删除。此时可以通过数组（单字段）或者ROW（多字段）记录已经访问过的记录，从而实现去重的目的。

参考：http://www.jasongj.com/sql/cte/

WITH使用注意事项

WITH中的数据修改语句会被执行一次，并且肯定会完全执行，无论主语句是否读取或者是否读取所有其输出。而WITH中的SELECT语句则只输出主语句中所需要记录数。
WITH中使用多个子句时，这些子句和主语句会并行执行，所以当存在多个修改子语句修改相同的记录时，它们的结果不可预测。
所有的子句所能“看”到的数据集是一样的，所以它们看不到其它语句对目标数据集的影响。这也缓解了多子句执行顺序的不可预测性造成的影响。
如果在一条SQL语句中，更新同一记录多次，只有其中一条会生效，并且很难预测哪一个会生效。
如果在一条SQL语句中，同时更新和删除某条记录，则只有更新会生效。
目前，任何一个被数据修改CTE的表，不允许使用条件规则，和ALSO规则以及INSTEAD规则。

示例

创建 COMPANY 表，数据内容如下：

本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

DROP TABLE COMPANY;
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

postgres=# select * from COMPANY;
 id | name  | age |                      address                       | salary
----+-------+-----+----------------------------------------------------+--------
  1 | Paul  |  32 | California                                         |  20000
  2 | Allen |  25 | Texas                                              |  15000
  3 | Teddy |  23 | Norway                                             |  20000
  4 | Mark  |  25 | Rich-Mond                                          |  65000
  5 | David |  27 | Texas                                              |  85000
  6 | Kim   |  22 | South-Hall                                         |  45000
  7 | James |  24 | Houston                                            |  10000
(7 rows)

DROP TABLE COMPANY;

CREATE TABLE COMPANY(

ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR(50),

SALARY REAL

);