SQL 单个查询中的多个 CTE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35248217/
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
Multiple CTE in single query
提问by axvm
Is it possible to combine multiple CTEs in single query with arel? I am looking for way to get result like this:
是否可以在单个查询中将多个 CTE 与arel? 我正在寻找获得这样的结果的方法：
WITH 'cte1' AS (
...
),
WITH RECURSIVE 'cte2' AS (
...
),
WITH 'cte3' AS (
...
)
SELECT ... FROM 'cte3' WHERE ...
As you can see, I have one recursive CTE and two non recursive.
如您所见，我有一个递归 CTE 和两个非递归。
回答by Erwin Brandstetter
Use the key word WITHonceat the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVEat the top oncealso, even if not all CTEs are recursive:
在顶部使用WITH一次关键字。如果您的任何公共表表达式 (CTE) 是递归的 (rCTE)，您也必须RECURSIVE在顶部添加一次关键字，即使并非所有 CTE 都是递归的：
WITH RECURSIVE
cte1 AS (...) -- can still be non-recursive
, cte2 AS (SELECT ...
UNION ALL
SELECT ...) -- recursive term
, cte3 AS (...)
SELECT ... FROM cte3 WHERE ...
If
RECURSIVEis specified, it allowsaSELECTsubquery to reference itself by name.
如果
RECURSIVE指定，它允许一个SELECT子查询通过名称引用自身。
Bold emphasis mine. And, even more insightful:
大胆强调我的。而且，更有见地：
Another effect of
RECURSIVEis thatWITHqueries need not be ordered: a query can reference another one that is later in the list. (However, circular references, or mutual recursion, are not implemented.) WithoutRECURSIVE,WITHqueries can only reference siblingWITHqueries that are earlier in theWITHlist.
的另一个效果
RECURSIVE是WITH查询不需要排序：一个查询可以引用列表中后面的另一个查询。（但是，没有实现循环引用或相互递归。）没有RECURSIVE，WITH查询只能引用列表WITH中较早的同级查询WITH。
Bold emphasis mine again. Meaning that the order of WITHclauses is meaninglesswhen the RECURSIVEkey word has been used.
再次大胆强调我的。这意味着当使用了关键字时，WITH从句的顺序是没有意义的RECURSIVE。
BTW, since cte1and cte2in the example are not referenced in the outer SELECTand are plain SELECTcommands themselves (no collateral effects), they are never executed (unless referenced in cte3).
顺便说一句，由于cte1和cte2在示例中没有在外部引用SELECT并且SELECT本身是普通命令（没有附带影响），因此它们永远不会执行（除非在 中引用cte3）。
回答by Gordon Linoff
Yes. You don't repeat the WITH. You just use a comma:
是的。你不要重复WITH. 您只需使用逗号：
WITH cte1 AS (
...
),
cte2 AS (
...
),
cte3 AS (
...
)
SELECT ... FROM 'cte3' WHERE ...
And: Only use single quotes for string and date constants. Don't use them for column aliases. They are not allowed for CTE names anyway.
并且：仅对字符串和日期常量使用单引号。不要将它们用于列别名。无论如何，它们不允许用于 CTE 名称。
