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

我正在简化一个复杂的 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 ';'.

可能不相关，但这是在 SQL2008上。

74526

小开

Above mentioned answer is right:

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

Aditionally, You can also query from cte1 in cte2 :

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


select * from cte1 union select * from cte2

val1,val2 are just asumptions for expressions..

Hope this blog will also help : http://iamfixed.blogspot.de/2017/11/common-table-expression-in-sql-with.html