Sql Server - 子查询中的用户CTE

This question has been asked before -

之前已经问过这个问题 -

How we can use CTE in subquery in sql server?

我们如何在sql server中的子查询中使用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 -

这有效,但我真的希望能够在以下场景中使用CTE -

as a subquery in a SELECT

作为SELECT中的子查询
as a derived table in the FROM clause of a SELECT

作为SELECT的FROM子句中的派生表

Both of these work in PostgreSQL. With Sql Server 2005, I get "Incorrect syntax near the keyword 'with'".

这两个都在PostgreSQL中工作。使用Sql Server 2005,我得到“关键字'附近的语法不正确'和'”。

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.

我希望它的原因是我的大多数查询都是动态构造的,我希望能够定义CTE,将其保存在某个地方,然后根据需要将其放入更复杂的查询中。

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根本不支持这种用法,我将不得不接受它,但我没有读过任何声明它不被允许的内容。

Does anyone know if it is possible to get this to work?

有谁知道是否可以让这个工作?

2 个解决方案

#1

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:

在SQL Server中,CTE必须位于查询的顶部。如果动态构造查询,除了查询之外,还可以存储CTE列表。在将查询发送到SQL Server之前,可以在查询前添加CTE列表:

; with Cte1 as (...definition 1...),
  Cte2 as (...definition 2...),
  Cte3 as (...definition 3...),
  ...
...constructed query...

This is assuming that you're constructing the SQL outside of SQL Server.

这假设您正在构建SQL Server之外的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.

您还可以考虑创建视图。视图可以包含CTE,它们可以用作子查询或派生表。如果您不经常生成SQL(例如仅在安装期间或作为部署的一部分),则视图是一个不错的选择。

#2

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.

SQL Server不支持这个非常必需的功能。我也一直在寻求帮助。 MS SQL Server不支持临时视图,而不支持PostgreSQL。上述解决方案也可能只有在事先生成所有CTE定义且每个子查询中都没有冲突名称时才有效 - 目的是这些CTE定义对于每个级别可能不同子查询。

Sad but true !!!

伤心但真实!

Regards, Kapil

#1