SQL 如何在单个 SELECT 语句中拥有多个公用表表达式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/584284/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I have multiple common table expressions in a single SELECT statement?
提问by Paul Rowland
I am in the process of simplifying a complicated select statement, so thought I would use common table expressions.
我正在简化一个复杂的 select 语句，所以我想我会使用公共表表达式。
Declaring a single cte works fine.
声明单个 cte 工作正常。
WITH cte1 AS (
SELECT * from cdr.Location
)
select * from cte1
Is it possible to declare and use more than one cte in the same SELECT?
是否可以在同一个 SELECT 中声明和使用多个 cte？
ie this sql gives an error
即这个sql给出了一个错误
WITH cte1 as (
SELECT * from cdr.Location
)
WITH cte2 as (
SELECT * from cdr.Location
)
select * from cte1
union
select * from cte2
the error is
错误是
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. 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 ';'.
Probably not relevant but this is on SQL 2008.
可能不相关，但这是在 SQL 2008 上。
回答by MarkusQ
I think it should be something like:
我认为它应该是这样的：
WITH
cte1 as (SELECT * from cdr.Location),
cte2 as (SELECT * from cdr.Location)
select * from cte1 union select * from cte2
Basically, WITHis just a clause here, and like the other clauses that take lists, "," is the appropriate delimiter.
基本上，WITH这里只是一个子句，就像其他带列表的子句一样，“，”是合适的分隔符。
回答by Sagar Dev Timilsina
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 :
另外，您还可以从 cte2 中的 cte1 查询：
WITH
cte1 as (SELECT * from cdr.Location),
cte2 as (SELECT * from cte1 where val1 = val2)
select * from cte1 union select * from cte2
val1,val2are just asumptions for expressions..
val1,val2只是表达式的假设..
Hope this blog will also help : http://iamfixed.blogspot.de/2017/11/common-table-expression-in-sql-with.html
希望这个博客也能有所帮助：http: //iamfixed.blogspot.de/2017/11/common-table-expression-in-sql-with.html
