Sql Server - 子查询中的用户CTE
这个问题之前已经被问过 -
建议的唯一答案是“只需在顶部定义 CTE 并在子查询中访问它？”
这可行，但我真的希望能够在以下场景中使用 CTE -
作为 SELECT 中的子查询
作为 FROM 子句中的派生表SELECT
这两者都在 PostgreSQL 中工作。使用 Sql Server 2005，我收到“关键字 'with' 附近的语法不正确”。
我想要它的原因是我的大多数查询都是动态构造的，我希望能够定义一个 CTE，将其保存在某个地方，然后根据需要将其放入更复杂的查询中。
如果 Sql Server 根本不支持这种用法，我将不得不接受它，但我没有读到任何声明不允许的内容。
有谁知道是否可以让它发挥作用？
This question has been asked before -
How we can use CTE in subquery in sql server?
The only answer suggested was "Just define your CTE on top and access it in the subquery?"
This works, but I would really like to be able to use a CTE in the following scenarios -
as a subquery in a SELECT
as a derived table in the FROM clause of a SELECT
Both of these work in PostgreSQL. With Sql Server 2005, I get "Incorrect syntax near the keyword 'with'".
The reason I would like it is that most of my queries are constructed dynamically, and I would like to be able to define a CTE, save it somewhere, and then drop it in to a more complex query on demand.
If Sql Server simply does not support this usage, I will have to accept it, but I have not read anything that states that it is not allowed.
Does anyone know if it is possible to get this to work?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
在 SQL Server 中，CTE 必须位于查询的顶部。如果动态构建查询，除了查询之外，还可以存储 CTE 列表。在将查询发送到 SQL Server 之前，您可以在查询前添加 CTE 列表作为前缀：
这假设您在 SQL Server 外部构建 SQL。
您还可以考虑创建视图。视图可以包含 CTE，并且它们可以用作子查询或派生表。如果您不经常生成 SQL（例如仅在安装期间或作为部署的一部分），那么视图是一个不错的选择。
In SQL Server, CTE's must be at the top of the query. If you construct queries dynamically, you could store a list of CTE's in addition to the query. Before you send the query to SQL server, you can prefix the query with a list of CTE's:
This is assuming that you're constructing the SQL outside of SQL Server.
You could also consider creating views. Views can contain CTE's, and they can be used as a subquery or derived table. Views are a good choice if you generate SQL infrequently, say only during an installation or as part of a deployment.
SQL Server 不支持这一急需的功能。我也一直在寻求这方面的帮助。
与 PostgreSQL 不同，MS SQL Server 也不支持临时视图。仅当所有 CTE 定义都可以预先生成并且在每个子查询中也不具有冲突的名称时，上述解决方案也可能有效 - 目的是这些 CTE 定义对于每个级别可能不同的子查询。
悲伤但真实！
问候，
卡皮尔
SQL Server does not support this much-required feature. I too have been looking for help on this.
MS SQL Server does not support Temporary Views either as opposed to PostgreSQL. The above-mentioned solution is also likely to work only if all the CTE definitions could be generated before-hand and do not have conflicting names in each of the sub-queries either - the purpose being that these CTE definitions may be different for each level of a sub-query.
Sad but true !!!
Regards,
Kapil