SQL2005 学习笔记 公用表表达式(CTE)

公用表表达式(CTE)是SQL 2005引入的一项非常强大的功能。可以把它想象成为一个临时性视图，从多张表中获取数据并创建一个只读的虚拟数据集。CTE提供了一种优雅而有力的方式，允许开发人员重用代码，同时还可以提供更好的可读性和可维护性。

CTE的基本语法

WITH [CTE名称] ([列名列表]) AS
(
    -- SELECT 语句
)

其中，WITH子句引入了一个公用表表达式，可以给这个公用表表达式指定名称，方便之后的引用。在AS的后面，可以指定公用表中输出的列名列表。

CTE的使用注意事项

以下是使用CTE时需要注意的一些事项：

• CTE只在包含它们的语句中可见，不能在同一语句块外部或其他查询中引用。
• CTE的生命周期只在包含它们的查询语句中，它们不会被存储在数据库中。
• CTE不能与SELECT INTO结合使用，也不能创建索引或统计信息。
• CTE不允许附加表提示或表达式。
• 在一个查询中可以使用多个CTE，但是同一个CTE名称只能在一个查询中使用一次。

CTE的示例

示例1：使用CTE计算销售订单总额

假设我们有两个表，一个是客户信息表OrderCustomers，包含客户ID和客户名称；另一个是销售订单表OrderSales，包含订单ID、客户ID和订单金额。

我们可以使用CTE来计算每一个客户的订单总额。

WITH CTE_Sales (CustomerID, TotalSales) AS
(
    SELECT CustomerID, SUM(SalesAmount) AS TotalSales
    FROM OrderSales
    GROUP BY CustomerID
)
SELECT C.CustomerName, S.TotalSales
FROM OrderCustomers C
JOIN CTE_Sales S ON C.CustomerID = S.CustomerID;

在上面的查询中，我们创建了一个名为CTE_Sales的公用表。这个公用表使用了OrderSales表，计算每个客户的销售总额。最后，我们将客户名称和销售总额从两个表中连接起来并输出。

示例2：使用CTE进行递归查询

CTE还可以用于递归查询。假设我们有一个包含员工信息的表Employee，每一个员工都有一个上级名称作为他们的Manager。我们想要查询一个员工的所有上级名称，包括这个员工本身。

首先，我们需要创建一个公用表，它包含一个包含根节点的“基本”查询和一个递归查询。

WITH CTE_Managers (EmployeeID, EmployeeName, ManagerID, ManagerName, ManagerHierarchy) AS
(
    -- 该查询选择每个员工的雇员名称和上级名称，并使用0作为根节点的ID。
    SELECT EmployeeID,
           EmployeeName,
           ManagerID,
           ManagerName,
           CAST(EmployeeID AS VARCHAR(MAX)) AS ManagerHierarchy
    FROM Employee
    WHERE EmployeeID = 1

    UNION ALL

    -- 递归查询，将每个节点的上级节点添加到结果集中。同时，每个节点的ManagerHierarchy列都将增加其祖先的EmployeeID。
    SELECT E.EmployeeID,
           E.EmployeeName,
           E.ManagerID,
           E.ManagerName,
           CAST(M.ManagerHierarchy + '.' + CAST(E.EmployeeID AS VARCHAR(MAX)) AS VARCHAR(MAX))
    FROM Employee E
    INNER JOIN CTE_Managers M
        ON E.EmployeeID = M.ManagerID
)

在上面的查询中，我们使用了一个基本查询和一个递归查询。对于基本查询，我们仅选择EmployeeID为1的员工作为根节点。而对于递归查询，则是找到每个节点的上级节点，并将每个节点的ManagerHierarchy列增加其祖先的EmployeeID。

最后，我们可以从CTE_Managers中选择某个员工的所有上级名称

SELECT EmployeeName, ManagerName
FROM CTE_Managers
WHERE ManagerHierarchy LIKE '%.1.%'
ORDER BY ManagerHierarchy;

在上面的查询中，我们可以通过LIKE运算符来选择一个员工的所有上级名称，使用ORDER BY ManagerHierarchy可以保证这些名称按照它们之间的级别顺序显示。