单个查询中有多个CTE

问题描述

是否可以通过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 ...

手册:

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

Bold emphasis mine. And, even more insightful:

再次强调我的粗体.这意味着使用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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！