Sql Server - 子查询中的用户 CTE
这个问题之前有人问过 -
This question has been asked before -
我们如何在sql子查询中使用CTE服务器?
建议的唯一答案是只需在顶部定义 CTE 并在子查询中访问它?"
The only answer suggested was "Just define your CTE on top and access it in the subquery?"
这可行，但我真的希望能够在以下场景中使用 CTE -
This works, but I would really like to be able to use a CTE in the following scenarios -
作为 SELECT 中的子查询
as a subquery in a SELECT
作为 SELECT 的 FROM 子句中的派生表
as a derived table in the FROM clause of a SELECT
这两种方法都适用于 PostgreSQL.使用 Sql Server 2005，我得到关键字 'with' 附近的语法不正确".
Both of these work in PostgreSQL. With Sql Server 2005, I get "Incorrect syntax near the keyword 'with'".
我想要它的原因是我的大多数查询都是动态构建的，我希望能够定义一个 CTE，将其保存在某个地方，然后根据需要将其放入更复杂的查询中.
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.
如果 Sql Server 根本不支持这种用法，我将不得不接受它，但我没有阅读任何声明它不允许的内容.
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.
有谁知道这是否可行?
推荐答案
在 SQL Server 中，CTE 必须位于查询的顶部.如果您动态构建查询，则除了查询之外，您还可以存储 CTE 列表.在将查询发送到 SQL 服务器之前，您可以在查询前加上 CTE 的列表:
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:
; with Cte1 as (...definition 1...),
Cte2 as (...definition 2...),
Cte3 as (...definition 3...),
...
...constructed query...
这是假设您在 SQL Server 之外构建 SQL.
This is assuming that you're constructing the SQL outside of SQL Server.
您也可以考虑创建视图.视图可以包含 CTE，它们可以用作子查询或派生表.如果您不经常生成 SQL(例如仅在安装期间或作为部署的一部分)，视图是一个不错的选择.
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.
相关文章