SQL Server-子查询中的用户CTE [英] Sql Server - user CTE in subquery

问题描述

此问题之前曾有人问过-

This question has been asked before -

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

1. 作为SELECT中的子查询

1. 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.

有人知道是否有可能将其用于此用途吗？

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

推荐答案

在SQL Server中，CTE必须位于查询的顶部。如果您动态构建查询，则除了查询之外，您还可以存储CTE的列表。在将查询发送到SQL Server之前，您可以在查询之前添加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...

您还可以考虑创建视图。视图可以包含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.

这篇关于SQL Server-子查询中的用户CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！