SQL DB2 带子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21998862/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DB2 With Clause
提问by grape_mao
I am new to DB2 and I have a question about the withclause. For example in the following query:
我是 DB2 新手，我有一个关于with子句的问题。例如在以下查询中：
WITH values AS
(
SELECT user_id, user_data FROM USER WHERE user_age < 20
)
SELECT avg(values.user_data) FROM values
UNION
SELECT sum(values.user_data) FROM values
How many times will the common table expression be executed? Will the result of the with clause be stored in a temporary table or it will do sub-select twice. (I use with and union here just to give an example, and sorry for my poor english)
公表表达式会执行多少次？with 子句的结果会存储在临时表中还是会执行两次子选择。（我在这里使用 with 和 union 只是为了举个例子，对不起我的英语不好）
回答by mustaccio
As @Vladimir Oselsky has mentioned, only looking at the execution plan will give you a definite answer. In this contrived example the CTE subselect will likely run twice.
正如@Vladimir Oselsky 提到的，只看执行计划会给你一个明确的答案。在这个人为的例子中，CTE 子选择可能会运行两次。
回答by Gordon Linoff
In DB2, common table expressions should create the Common Table Expression Node in the execution plan (see the documentation here). This node explicitly says:
在 DB2 中，公用表表达式应该在执行计划中创建公用表表达式节点（请参阅此处的文档）。该节点明确表示：
They serve as intermediate tables. Traditionally, a nested table expression also serves this purpose. However, a common table expression can be referenced multiple times after it is instantiated; nested table expressions cannot.
它们用作中间表。传统上，嵌套表表达式也用于此目的。但是，一个公用表表达式在实例化后可以被多次引用；嵌套表表达式不能。
I read this as saying that the CTE is only evaluated once, instantiated, and then used multiple times. Also, if the CTE is referenced only one time, the "instantiation" is optimized away.
我读这句话是说 CTE 只评估一次，实例化，然后多次使用。此外，如果 CTE 仅被引用一次，则“实例化”将被优化掉。
Note that this is the way that Postgres handles CTEs (materialized subqueries) and not the way the SQL Server handles them.
请注意，这是 Postgres 处理 CTE（具体化子查询）的方式，而不是 SQL Server 处理它们的方式。
