SQLServer存储过程关于公用表表达式(CTE)和临时表的实例教程

在 SQL Server 中，公用表表达式（Common Table Expressions，简称 CTE）和临时表（Temporary Tables）是处理复杂数据集时常用的两种方法。它们各有优势，具体选择哪一种取决于你的具体需求。下面我会通过实例分别介绍如何使用 CTE 和临时表。

1. 公用表表达式 (CTE)

CTE 可以让你将一个复杂的子查询定义为一个临时的结果集，这个结果集可以在同一个查询中多次引用，使查询更加清晰和易于维护。CTE 是在 WITH 子句中定义的，并且只在当前查询执行周期内存在。

示例：使用 CTE 计算员工部门的平均薪资

假设我们有两张表：Employees 和 Departments。

• Employees 表结构：EmployeeID, Name, Salary, DepartmentID
• Departments 表结构：DepartmentID, DepartmentName

我们想找出每个部门的平均薪资。

WITH DepartmentAverages AS (
    SELECT d.DepartmentName, AVG(e.Salary) as AverageSalary
    FROM Employees e
    JOIN Departments d ON e.DepartmentID = d.DepartmentID
    GROUP BY d.DepartmentName
)
SELECT * FROM DepartmentAverages
WHERE AverageSalary > 50000; -- 假设我们只对平均薪资超过50000的部门感兴趣

2. 临时表 (Temporary Tables)

临时表是在内存中创建的表，也可以在磁盘上创建，其生命周期可以是事务级别的，也可以是会话级别的。临时表对于需要在多个查询或存储过程中重复使用的中间结果非常有用。

示例：使用临时表统计员工部门的平均薪资

同样的场景，但这次我们使用临时表。

CREATE TABLE #TempDepartmentAverages (
    DepartmentName VARCHAR(100),
    AverageSalary DECIMAL(10,2)
);

INSERT INTO #TempDepartmentAverages (DepartmentName, AverageSalary)
SELECT d.DepartmentName, AVG(e.Salary)
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName;

SELECT * FROM #TempDepartmentAverages
WHERE AverageSalary > 50000;

DROP TABLE #TempDepartmentAverages;

存储过程中的使用

无论是 CTE 还是临时表，都可以在存储过程中使用。下面是一个使用 CTE 的存储过程示例：

CREATE PROCEDURE usp_GetDepartmentAverages
AS
BEGIN
    WITH DepartmentAverages AS (
        SELECT d.DepartmentName, AVG(e.Salary) as AverageSalary
        FROM Employees e
        JOIN Departments d ON e.DepartmentID = d.DepartmentID
        GROUP BY d.DepartmentName
    )
    SELECT * FROM DepartmentAverages
    WHERE AverageSalary > 50000;
END;
GO

同样地，你也可以在存储过程中使用临时表。这两种方法的选择主要取决于性能需求、数据量以及代码的可读性和可维护性。CTE 通常用于简单的数据转换和聚合，而临时表更适合处理大量数据或需要多次访问相同中间结果的情况。