PG中的CTE(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的语法:
1 2 3 4 5 | 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
的基本价值是将复杂的查询分解称为简单的部分。一个例子:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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的整数合的查询:
1 2 3 4 5 6 | 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
),抛弃重复行。把所有剩余的行包括在递归查询的结果中,并且也把它们放在一个临时的工作表中。 - 只要工作表不为空,重复下列步骤:
- 计算递归项,用当前工作表的内容替换递归自引用。对
UNION
(不是UNION ALL
),抛弃重复行以及那些与之前结果行重复的行。将剩下的所有行包括在递归查询的结果中,并且也把它们放在一个临时的中间表中。 - 用中间表的内容替换工作表的内容,然后清空中间表。
- 计算递归项,用当前工作表的内容替换递归自引用。对
注意
严格来说,这个处理是迭代而不是递归,但是RECURSIVE
是SQL标准委员会选择的术语。
在上面的例子中,工作表在每一步只有一个行,并且它在连续的步骤中取值从1到100。在第100步,由于WHERE
子句导致没有输出,因此查询终止。
递归查询通常用于处理层次或者树状结构的数据。一个有用的例子是这个用于找到一个产品的直接或间接部件的查询,只要给定一个显示了直接包含关系的表:
1 2 3 4 5 6 7 8 9 10 | 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
的查询:
1 2 3 4 5 6 7 8 9 | 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
:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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
:
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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
时会永远循环:
1 2 3 4 5 6 | 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
查询的完整输出的一小部分。
这些规则的一个简单示例是
1 2 3 4 | WITH w AS ( SELECT * FROM big_table ) SELECT * FROM w WHERE key = 123; |
这个 WITH
查询将被合并,生成相同的执行计划为
1 | SELECT * FROM big_table WHERE key = 123; |
特别是,如果在key
上有一个索引,它可能只用于获取具有 key = 123
的行。 另一方面,在
1 2 3 4 5 | 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
的临时拷贝,然后与其自身 — 联合,这样将不能从索引上获得任何好处。 如果写成下面的形式,这个查询将被执行得更有效率。
1 2 3 4 5 | 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
可能不理想的例子为
1 2 3 4 | 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
)。这允许你在同一个查询中执行多个而不同操作。一个例子:
1 2 3 4 5 6 7 8 9 | 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
子句,则它形不成临时表并且不能在剩余的查询中被引用。但是这样一个语句将被执行。一个非特殊使用的例子:
1 2 3 4 | WITH t AS ( DELETE FROM foo ) DELETE FROM bar; |
这个例子将从表foo
和bar
中移除所有行。被报告给客户端的受影响行的数目可能只包括从bar
中移除的行。
数据修改语句中不允许递归自引用。在某些情况中可以采取引用一个递归WITH
的输出来操作这个限制,例如:
1 2 3 4 5 6 7 8 9 | 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
子语句和主查询之间传达改变的唯一方法。其例子
1 2 3 4 5 | WITH t AS ( UPDATE products SET price = price * 1.05 RETURNING * ) SELECT * FROM products; |
外层SELECT
可以返回在UPDATE
动作之前的原始价格,而在
1 2 3 4 5 | 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
1 2 3 4 5 6 | CREATE TABLE chinamap ( id INTEGER, pid INTEGER, name TEXT ); |
需要查出某个省,比如湖北省,管辖的所有市及市辖地区,可以通过WITH RECURSIVE来实现,如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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; |
结果如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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.执行
1 2 3 4 5 | SELECT id, name FROM chinamap WHERE id = 11 |
结果集和working table为
1 | 11 | 湖北 |
2.执行
1 2 3 4 5 6 | SELECT origin.id, result.name || ' > ' || origin.name FROM result JOIN chinamap origin ON origin.pid = result.id |
结果集和working table为
1 2 3 4 5 6 7 8 | 110 | 湖北省 > 武汉市 120 | 湖北省 > 孝感市 130 | 湖北省 > 宜昌市 140 | 湖北省 > 随州市 150 | 湖北省 > 仙桃市 160 | 湖北省 > 荆门市 170 | 湖北省 > 枝江市 180 | 湖北省 > 神农架市 |
3.再次执行recursive query,结果集和working table为
1 2 3 4 5 6 7 | 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 表,数据内容如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | 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) |
下面将使用 WITH 子句在上表中查询数据:
1 2 3 4 5 6 7 8 9 | With CTE AS (Select ID , NAME , AGE , ADDRESS , SALARY FROM COMPANY ) Select * From CTE; |
得到结果如下:
1 2 3 4 5 6 7 8 9 10 | 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) |
接下来让我们使用 RECURSIVE 关键字和 WITH 子句编写一个查询,查找 SALARY(工资) 字段小于 20000 的数据并计算它们的和:
1 2 3 4 5 6 | WITH RECURSIVE t(n) AS ( VALUES (0) UNION ALL SELECT SALARY FROM COMPANY WHERE SALARY < 20000 ) SELECT sum(n) FROM t; |
得到结果如下:
1 2 3 4 | sum ------- 25000 (1 row) |
下面我们建立一张和 COMPANY 表相似的 COMPANY1 表,使用 DELETE 语句和 WITH 子句删除 COMPANY 表中 SALARY(工资) 字段大于等于 30000 的数据,并将删除的数据插入 COMPANY1 表,实现将 COMPANY 表数据转移到 COMPANY1 表中:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ); WITH moved_rows AS ( DELETE FROM COMPANY WHERE SALARY >= 30000 RETURNING * ) INSERT INTO COMPANY1 (SELECT * FROM moved_rows); |
得到结果如下:
1 | INSERT 0 3 |
此时,CAMPANY 表和 CAMPANY1 表的数据如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | runoobdb=# SELECT * FROM COMPANY; id | name | age | address | salary ----+-------+-----+------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 7 | James | 24 | Houston | 10000 (4 rows) runoobdb=# SELECT * FROM COMPANY1; id | name | age | address | salary ----+-------+-----+-------------+-------- 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 (3 rows) |
总结
使用CTE有以下优点:
- 提升复杂查询的可读性。我们可以使用CTE以更加可读的方式组织复杂查询
- 能够创建递归查询。递归查询是引用自身,当需要查询层次数据如组织或物料清单信息时,使用递归查询很方便。
- 和窗口函数一起使用。通过和窗口函数一起创建初始结果集,然后使用select对结果进一步进行处理。
CTE 优缺点:
- 可以使用递归 WITH RECURSIVE,从而实现其它方式无法实现或者不容易实现的查询
- 当不需要将查询结果被其它独立查询共享时,它比视图更灵活也更轻量
- CTE只会被计算一次,且可在主查询中多次使用
- CTE可极大提高代码可读性及可维护性
- CTE不支持将主查询中where后的限制条件push down到CTE中,而普通的子查询支持