如何在单个SELECT语句中具有多个公用表表达式？ [英] How can I have multiple common table expressions in a single SELECT statement?

问题描述

我正在简化一个复杂的select语句，因此以为我会使用公用表表达式。

I am in the process of simplifying a complicated select statement, so thought I would use common table expressions.

声明单个cte效果很好。

Declaring a single cte works fine.

WITH cte1 AS (
    SELECT * from cdr.Location
    )

select * from cte1 

是否可以在同一SELECT中声明并使用多个cte？

Is it possible to declare and use more than one cte in the same SELECT?

即此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.

NB。我尝试放入分号并得到此错误

NB. I have tried putting semicolons in and get this error

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

可能不相关，但这与SQL 2008有关。

Probably not relevant but this is on SQL 2008.

推荐答案

我认为应该是这样的：

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

基本上， WITH 只是这里的一个子句，和其他带有列表的子句一样， 作为适当的分隔符。

Basically, WITH is just a clause here, and like the other clauses that take lists, "," is the appropriate delimiter.

这篇关于如何在单个SELECT语句中具有多个公用表表达式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！