I have a properly working CTE query. I'm using this query in an application which does select on top of it and giving error
我有一个正常工作的CTE查询。我在一个应用程序中使用这个查询，它在查询的顶部进行选择并产生错误
Incorrect syntax near WITH
与附近的错误语法
Is there any way I can do select on top of WITH?
有什么办法可以在上面进行选择吗?
select(columns) from
(WITH CTE AS(
#code
))
3 个解决方案
#1
3
Unfortunately SQL Server does not support this kind of syntax DBFiddle Demo. You need to rewrite your query:
不幸的是，SQL Server不支持这种语法DBFiddle演示。你需要重写你的查询:
WITH CTE AS(
#code
)
SELECT *
FROM CTE;
Or wrap you query with view and select from it instead.
或者用视图包装查询并从中进行选择。
#2
1
You declare the CTE first, then you select from it in the following statement
首先声明CTE，然后在下面的语句中从中选择
;WITH cte AS
(
select 1 AS col
)
SELECT col
FROM cte
#3
0
Remove the with Clause and do
删除with子句并执行
select col1, col2, col3 from
(select col1, col2, col3 from table ) as CTE
only drawback is that the derived table can be used only once not like a true CTE
惟一的缺点是派生表只能使用一次，不像真正的CTE
#1
3
Unfortunately SQL Server does not support this kind of syntax DBFiddle Demo. You need to rewrite your query:
不幸的是，SQL Server不支持这种语法DBFiddle演示。你需要重写你的查询:
WITH CTE AS(
#code
)
SELECT *
FROM CTE;
Or wrap you query with view and select from it instead.
或者用视图包装查询并从中进行选择。
#2
1
You declare the CTE first, then you select from it in the following statement
首先声明CTE，然后在下面的语句中从中选择
;WITH cte AS
(
select 1 AS col
)
SELECT col
FROM cte
#3
0
Remove the with Clause and do
删除with子句并执行
select col1, col2, col3 from
(select col1, col2, col3 from table ) as CTE
only drawback is that the derived table can be used only once not like a true CTE
惟一的缺点是派生表只能使用一次，不像真正的CTE