单个查询中有多个CTE [英] Multiple CTE in single query

问题描述

是否可以通过arel在单个查询中组合多个CTE?我正在寻找获得这样结果的方法:

Is it possible to combine multiple CTEs in single query with arel? I am looking for way to get result like this:

WITH 'cte1' AS (
...
),
WITH RECURSIVE 'cte2' AS (
...
),
WITH 'cte3' AS (
...
)
SELECT ... FROM 'cte3' WHERE ...

如您所见，我有一个递归CTE和两个非递归CTE.

As you can see, I have one recursive CTE and two non recursive.

推荐答案

在顶部使用关键字WITH 一次.如果您的任何通用表表达式(CTE)是递归(rCTE)，则即使并非所有CTE都是递归的，您也必须在顶部一次处添加关键字RECURSIVE:

Use the key word WITH once at the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE at the top once also, even if not all CTEs are recursive:

WITH RECURSIVE
  cte1 AS (...)         -- can still be non-recursive
, cte2 AS (SELECT ...
           UNION ALL
           SELECT ...)  -- recursive term
, cte3 AS (...)
SELECT ... FROM cte3 WHERE ...

手册:

如果指定了RECURSIVE，则它允许一个SELECT子查询来 引用自己的名字.

If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name.

强调粗体.而且，更有见识:

Bold emphasis mine. And, even more insightful:

RECURSIVE的另一个效果是，无需订购WITH个查询: 一个查询可以引用列表中后面的另一个查询. (然而， 循环引用或相互递归未实现.) 没有RECURSIVE，WITH查询只能引用同级WITH WITH列表中较早的查询.

Another effect of RECURSIVE is that WITH queries 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.) Without RECURSIVE, WITH queries can only reference sibling WITH queries that are earlier in the WITH list.

再次强调我的粗体.这意味着使用RECURSIVE关键字时WITH子句的顺序是无意义.

Bold emphasis mine again. Meaning that the order of WITH clauses is meaningless when the RECURSIVE key word has been used.

BTW，因为示例中的cte1和cte2不在外部SELECT中引用，并且它们本身是普通的SELECT命令(无附带影响)，所以它们从不执行(除非在cte3中引用) ).

BTW, since cte1 and cte2 in the example are not referenced in the outer SELECT and are plain SELECT commands themselves (no collateral effects), they are never executed (unless referenced in cte3).

这篇关于单个查询中有多个CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！