单个查询中的多个 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 和两个非递归.

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

推荐答案

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

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查询只能引用兄弟WITHWITH 列表中较早的查询.

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.

顺便说一句，因为示例中的 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屋！