SELECT类型查询是唯一可以嵌套的类型吗? [英] Are SELECT type queries the only type that can be nested?

问题描述

是否可以将非选择查询(更新，插入，删除)嵌入到另一个查询中?

Is it possible to have an non-select query (update, insert, delete) embedded into another query?

类似(select内的插入内容)

Something like (an insert inside a select)

A single query:

select such,and,such from .... where .... insert into .... ;

推荐答案

基本答案

有 CTE (公用表表达式)在 Postgres 中(就像在除MySQL外的任何主要现代RDBMS中一样).自9.1版开始，其中包含数据修改CTE .这些可以嵌套".
更新: MySQL 8.0最终添加了CTE

Basic answer

There are CTEs (Common Table Expressions) in Postgres (like in any major modern RDBMS except MySQL). Since version 9.1 that includes data-modifying CTEs. Those can be "nested".
Update: MySQL 8.0 finally adds CTEs.

与子查询不同，CTE构成了优化障碍.查询计划程序不能将琐碎的命令内联到主命令中，也不能对主查询和CTE之间的联接重新排序.子查询也可能如此.








无论哪种方式，CTE都比子查询需要更多的开销(性能成本).

Unlike subqueries CTEs pose as optimization barriers. The query planner cannot inline trivial commands into the main command or reorder joins among main query and CTEs. The same is possible with subqueries. May be (very) good or (very) bad for performance, it depends.
Either way, CTEs require a bit more overhead (performance cost) than subqueries.

您的问题是非常基本的，以上内容可能足以回答.但是，我将向高级用户添加一些内容(以及一个显示语法的代码示例).

Your question is very basic, the above is probably enough to answer. But I'll add a bit for advanced users (and a code example to show the syntax).

查询的所有CTE基于数据库的相同快照.下一个CTE可以重用先前CTE的 output (内部临时表)，但是对其他CTE而言，对基础表的影响是不可见的.多个CTE的顺序是任意的 ，除非INSERT，UPDATE，DELETE的RETURNING子句返回了某些内容-与SELECT无关，因为它不会更改任何内容，并且只是从快照中读取.

All CTEs of a query are based off the same snapshot of the database. The next CTE can reuse the output of previous CTEs (internal temporary tables), but effects on underlying tables are invisible for other CTEs. The sequence of multiple CTEs is arbitrary unless something is returned with the RETURNING clause for INSERT, UPDATE, DELETE - irrelvant for SELECT, since it does not change anything and just reads from the snapshot.

这可能会对多个更新产生微妙的影响，这些更新可能会影响同一行.只有一个更新可以影响每一行.哪个受CTE顺序的影响.

That can have subtle effects with multiple updates that would be affecting the same row. Only one update can affect each row. Which one is influenced by the sequence of CTEs.

尝试预测结果:

CREATE TEMP TABLE t (t_id int, txt text);
INSERT INTO t VALUES (1, 'foo'), (2, 'bar'), (3, 'baz');

WITH sel AS (SELECT * FROM t)
   , up1 AS (UPDATE t SET txt = txt || '1' WHERE t_id = 1 RETURNING *)
   , up2 AS (UPDATE t SET txt = t.txt || '2'
             FROM   up1
             WHERE  up1.t_id = t.t_id
             RETURNING t.*)
   , ins AS (INSERT INTO t VALUES (4, 'bamm'))
   , up3 AS (UPDATE t SET txt = txt || '3' RETURNING *)
SELECT 'sel' AS source, * FROM sel
UNION ALL
SELECT 'up1' AS source, * FROM up1
UNION ALL
SELECT 'up2' AS source, * FROM up2
UNION ALL
SELECT 'up3' AS source, * FROM up3
UNION ALL
SELECT 't'   AS source, * FROM t;

SQL提琴

别失望，我怀疑这里有很多人可以做到这一点. :)
其要点是:避免 CTE中的命令冲突.

Don't be disappointed, I doubt there are many here who could have done it. :)
The gist of this: avoid conflicting commands in CTEs.

这篇关于SELECT类型查询是唯一可以嵌套的类型吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！