SQL SERVER 2008 中的公共表表达式 (CTE)

引言

对于开发人员来说，编写或阅读使用大量 Join 的复杂 SQL 查询是一件令人头疼的事情。 使用 CTE 或公共表表达式可以更容易地理解和维护复杂的 SQL 语句。 在这篇文章中，我将尝试介绍在使用 CTE 时获得的一些好处。

在处理子查询时，通常需要从子查询中选择一部分数据，甚至需要将查询中的数据与一些其他表连接起来。 在这种情况下，您可以选择用别名命名您的子查询，或者直接使用它。 逐渐地，您的需求变得越来越复杂，您的查询在任何时候都可能看起来难以维护。 CTE 允许您一次定义子查询，使用别名命名它，然后在以后使用别名调用相同的数据，就像您对普通表所做的那样。 CTE 是标准的 ANSI SQL 标准。

Using the Code

例如，您有一个像这样的查询

SELECT * FROM  (
        SELECT A.Address, E.Name, E.Age From Address A
        Inner join Employee E on E.EID = A.EID) T
WHERE T.Age > 50
ORDER BY T.NAME

这个查询看起来真的很乱。 即使我需要编写一些围绕整个查询的内容，它也会逐渐变得难以阅读。 CTE 允许您预先生成表，并在以后当我们实际将数据绑定到输出时使用它。

使用 CTE 表达式重写查询看起来像

With T(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.Age > 50
ORDER BY T.NAME

是的，正如您所看到的，使用 CTE 的第二个查询更易于阅读。 您可以指定任意数量的查询表达式，最终将数据输出到外部环境的最终查询将最终引用所有这些表达式。

With T1(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
),
T2(Name, Desig)
AS
(
SELECT NAME, DESIG FROM Designation)
SELECT T1.*, T2.Desig FROM T1  --SELECT or USE CTE temporary Table
WHERE T1.Age > 50 AND T1.Name = T2.Name
ORDER BY T1.NAME

因此，查询使用逗号分隔。 因此，基本上您可以传递任意数量的查询，这些查询将充当子查询，获取您的数据并将其命名为查询中的临时表。

根据语法，CTE 以 With 子句开头。 您可以在大括号中指定列名，但这不是强制性的。

公共表表达式语法

公共表表达式包含三个核心部分

• CTE 名称（这是 WITH 关键字后面的内容）
• 列列表（可选）
• 查询（出现在 AS 关键字后面的括号内）

使用 CTE 的查询必须是出现在 CTE 之后的第一个查询。

何时使用公共表表达式

公共表表达式提供与视图相同的功能，但非常适合一次性使用，在这种情况下您不一定需要为系统定义视图。 即使不一定需要 CTE，它也可以提高可读性。 在使用公共表表达式中，Microsoft 提供了 CTE 的以下四个优点

• 创建递归查询。
• 在不需要视图的常规使用时替换视图；也就是说，您不必将定义存储在元数据中。
• 允许按从标量子查询或函数派生的列进行分组，该函数是不确定的或具有外部访问权限。
• 在同一语句中多次引用结果表。

使用 CTE 具有提高可读性和易于维护复杂查询的优点。 该查询可以分为独立的、简单的、逻辑构建块。 然后可以使用这些简单的块来构建更复杂的临时 CTE，直到生成最终结果集。

使用标量子查询（例如，我们在本文中看到的 (SELECT COUNT(1) FROM ...) 示例）不能在包含查询中直接分组或过滤。 同样，当使用 SQL Server 2005 的排名函数 - ROW_NUMBER()、RANK()、DENSE_RANK() 等时 - 包含查询不能包含过滤器或分组表达式，以仅返回已排名的结果的子集。 对于这两种情况，CTEs 都非常方便。

CTEs 也可以用来递归地枚举分层数据。

关注点

看看 CTE 的糟糕系列

WITH ShowMessage(STATEMENT, LENGTH)
AS
(
SELECT STATEMENT = CAST('I Like ' AS VARCHAR(300)), LEN('I Like ')
UNION ALL
SELECT
      CAST(STATEMENT + 'CodeProject! ' AS VARCHAR(300))
      , LEN(STATEMENT) FROM ShowMessage
WHERE LENGTH < 300
)
SELECT STATEMENT, LENGTH FROM ShowMessage

这将产生这样的结果

历史

• 2011 年 10 月 6 日：初始版本