如何在SQL Server中的子查询中使用公共表表达式（CTE）？

Question

我们如何在SQL Server中的子查询中使用CTE？

例如：

SELECT id (我想在这里使用CTE), name FROM table_name

- Paresh

---

叹息。为什么不提供更多的背景信息呢？例如...你想要实现什么？你为什么认为需要在这里使用CTE？你已经尝试了什么？为什么它没有起作用？等等。 - Greg Beech

叹气。叹气。为什么每个人都想要这么多上下文信息？TSQL的语法是固定的，我们不需要在执行语句之前说服SQL服务器结果有价值。在这个网站上提供的上下文越多，就越有可能被管理员认为无法理解而关闭。叹气。 - Ronnie Overby

完全同意 @RonnieOverby 的观点。这里的语境是如何在子查询中使用公共表表达式 (CTE)，标题已经给出了提示。以下是一个更清晰的版本：请解释如何执行以下查询：SELECT * FROM (WITH cte AS ( 一个好的 SELECT 语句) SELECT 一些字段、计算或聚合 FROM cte) AS x _可能会连接其他表_。 - Reversed Engineer

好的，这里是一些背景信息：我正在使用 SQL 构建规则引擎，其中规则由用户定义。因此，我有一个存储过程，它根据规则动态生成 SQL。SP 返回一个生成的 SQL 语句，例如 WITH cte1()，cte2() 等等。SELECT ... FROM cte1 JOIN cte2 ON... 现在，我想要另一个 SP 生成结果的摘要，例如 SELECT COUNT(*) FROM (%s)，其中 %s 是第一个 SP 生成的 SQL。我知道动态 SQL 的安全风险。动态生成 SQL 对于这个应用程序——即规则引擎是必需的。 - Reversed Engineer

https://learnsql.com/blog/sql-recursive-cte/ - Billu

Answer 1

在顶部定义你的CTE，然后在子查询中访问它即可。

WITH YourCTE(blubb) AS
(
    SELECT 'Blubb'
)
SELECT id,
       (SELECT blubb FROM YourCTE),
       name
FROM   table_name

- Maximilian Mayerl

---

无法减轻相关子查询。 - Vadzim

没有办法将一个条件传递给WITH语句，因此这样做没有意义。 - Silencer

Answer 2

它不起作用：

select id (I want to use CTE here), name from table_name

在子查询中无法使用公用表表达式（CTE）。

您可以将其视为一种解决方法：

CREATE VIEW MyCTEView AS ..here comes your CTE-Statement.

那么你就能够做到这件事：

select id (select id from MyCTEView), name from table_name

- Alfred E. Mustermann

---

我曾多次希望能够像派生表一样使用CTE。如果有一种不需要创建数据库对象（临时）的方法来实现这一点，我会非常喜欢它。 - Ronnie Overby

Answer 3

创建一个包含CTE的视图/多个CTE的视图，使用UNION将所有CTE集合起来。

CREATE VIEW [dbo].[_vEmployees] 
AS 
    WITH 
    TEST_CTE(EmployeeID, FirstName, LastName, City, Country)
        AS (
            SELECT EmployeeID, FirstName, LastName, City, Country FROM Employees WHERE EmployeeID = 4
        ), 
    TEST_CTE2
        AS (
            SELECT EmployeeID, FirstName, LastName, City, Country FROM Employees WHERE EmployeeID = 7
        )
    SELECT EmployeeID, FirstName, LastName, City, Country FROM TEST_CTE UNION SELECT * FROM TEST_CTE2
GO

现在，将其用于子查询中。

SELECT * FROM Employees WHERE EmployeeID IN (SELECT EmployeeID FROM _vEmployees)

- Bhuwan Maharjan

---

感谢Bhuwan Maharjan，我想创建一个带有CTE的视图，这很有帮助。赞！！！ - bismi

这并没有回答问题。要创建视图，您需要高级权限。 - edc65

我有一个类似的情况。在我的情况下，这是针对一个COTS产品（MicroFocus SBM）。我提供的SQL被系统包装并作为子查询运行：“SELECT * FROM（我的SQL在这里）”。我意识到这极大地限制了我的选择。 - undefined