如何在单个SELECT语句中使用多个共同表达式？

Question

我正在简化一条复杂的SELECT语句，所以想使用公共表达式。

声明单个CTE没有问题。

WITH cte1 AS (
    SELECT * from cdr.Location
    )

select * from cte1 

在同一个SELECT语句中声明和使用多个CTE是否可行？

即此SQL会产生错误

WITH cte1 as (
    SELECT * from cdr.Location
)

WITH cte2 as (
    SELECT * from cdr.Location
)

select * from cte1    
union     
select * from cte2

错误信息为

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

注意：我已经尝试过加入分号，但是仍然出现这个错误。

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ';'.

可能不相关，但这是关于 SQL 2008 的。

- Paul

Answer 1

我认为应该是这样的：

WITH 
    cte1 as (SELECT * from cdr.Location),
    cte2 as (SELECT * from cdr.Location)
select * from cte1 union select * from cte2

基本上，WITH 在这里只是一个从句，就像其他需要列表的从句一样，"," 是适当的分隔符。

- MarkusQ

---

太棒了。我一直在使用CTE的结果填充临时表，然后再进行组合，但是在将其打包到存储过程中时遇到了分号的问题。不错的方法！ - Tom Halladay

请注意，cte2可以像这样引用cte1：... cte2 as (SELECT * FROM cte1 WHERE ...) - Chahk

英雄！这让我困扰了好几个小时。 - bjpelcdev

声明递归和非递归表达式怎么样？ - Dmitry Volkov

Answer 2

上述答案是正确的：

WITH 
    cte1 as (SELECT * from cdr.Location),
    cte2 as (SELECT * from cdr.Location)
select * from cte1 union select * from cte2

此外，您还可以从cte2中查询cte1:

WITH 
    cte1 as (SELECT * from cdr.Location),
    cte2 as (SELECT * from cte1 where val1 = val2)

select * from cte1 union select * from cte2

< p > < code >val1,val2< /code > 只是表达式的假设.. < /p > < p >希望这篇博客也能帮到您： http://iamfixed.blogspot.de/2017/11/common-table-expression-in-sql-with.html< /p >

- Sagar Dev Timilsina

---

声明递归和非递归表达式怎么样？ - Dmitry Volkov