如何在单个SQL查询中使用多个CTE？

Question

是否可以将多个CTE组合在单个查询中？

我正在寻找这样的结果：

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

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

Answer 1

在顶部使用 的关键词。如果您的任何常见表表达式（CTE）是递归（RCTE），则必须在top 上添加关键字递归，即使并非所有CTE都是递归：

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

<

如果指定递归，则允许 a 选择子查询到
用名称引用自己。

大胆的重点是我的。而且，更具洞察力：

递归的另一个效果是，不必订购 ：
查询可以引用列表以后的另一个查询。 （然而，
未实施循环引用或相互递归。）
没有递归，查询只能用引用
列表中中较早的查询。




再次强调我的。这意味着使用条款的的顺序是 时使用递归键>关键词。

顺便说一句，由于cte1和cte2在示例中未引用select和plain select select 命令自己（没有附带效果），它们从未执行过（除非cte3中引用）。

Answer 2

是的。您不必重复WITH。您只需使用逗号：

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

And: 仅对字符串和日期常量使用单引号。不要将它们用作列别名。无论如何，它们不允许用于 CTE 名称。

Answer 3

正如已接受的答案所正确指出的那样，with 子句每个 CTE 链仅使用一次。不过，为了完整起见，我想补充一下它不会阻止您嵌套 CTE。

如果cte2使用cte1，cte3使用cte2等，那么CTE之间的依赖链是线性的，并且它是表示为 with，具有 3 个 CTE。相反，如果 cte2 不需要 cte1 并且两者仅在 cte3 中需要，则应考虑将它们嵌套在 cte1 的定义下code>cte3（with cte3 as（with cte1 as (...), cte2 as (...) select...)）。

然后，CTE 的语法反映了 CTE 之间的依赖关系树，并从字面上可视化部分数据集的范围，这可以提高可读性并防止范围泄漏错误。并非所有数据库供应商都支持它，但 Postgres 支持。

示例：

with cte1(id,capital) as (
  values(1,'Prague'),(2,'Bratislava')
), cte2(id,code) as (
  with cte2inner1(id,code) as (
    values(1,'CZ'),(2,'SK')
  ), cte2inner2(id,country) as (
    values(1,'Czech Republic'),(2,'Slovakia')
  )
  select id,country from cte2inner1 join cte2inner2 using (id)
) 
select *
from cte1 join cte2 using (id)
--join cte2inner1  not possible here

Answer 4

问题原因：在这里，您不必使用多个WITH子句来组合多个CTE。

解决方案：可以在SQL 中使用单个WITH 子句创建多个公共表表达式。两个不同的 CTE 使用单个 WITH 子句创建，并用逗号分隔以创建多个 CTE。

使用单个 With 子句的多个 CTE 示例

With EmpCount1(DeptName,TotalEmployees)
as
  (
   Select DeptName, COUNT(*) as TotalEmployees
   from Tbl_EmpDetails
   join Tbl_Dept Dept
   on Tbl_EmpDetails.DeptId = Dept.DeptId
   WHERE DeptName IN ('BI','DOTNET')
   group by DeptName
  ),
EmpCount2(DeptName,TotalEmployees)
as
  (
   Select DeptName, COUNT(*) as TotalEmployees
   from Tbl_EmpDetails
   join Tbl_Dept Dept
   on Tbl_EmpDetails.DeptId = Dept.DeptId
   WHERE DeptName IN ('JAVA','AI')
   group by DeptName
  )


  Select * from EmpCount1
  UNION
  Select * from EmpCount2

这是使用单个 With 子句创建多个公共表表达式的示例语法。