SQL中的公共表表达式（CTEs）

公共表表达式（CTEs）是SQL中的一种临时结果集，可以在SELECT、INSERT、UPDATE或DELETE语句中引用。它们可用于简化复杂查询并提高可读性。以下是三个实际示例的详细说明，包括表创建脚本和示例数据，以便端到端执行。

简单用法：选择数据

场景

假设您有一个员工表，并且想要获取员工列表及其部门名称。

SQL代码

-- 创建部门表
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);

-- 创建员工表
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- 插入部门样本数据
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
    (1, 'HR'),
    (2, 'Finance'),
    (3, 'IT'),
    (4, 'Accounts');

-- 插入员工样本数据
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID) VALUES
    (101, 'Devesh', 1),
    (102, 'Pankaj Kumar', 2),
    (103, 'Sanjay Kumar', 3),
    (104, 'Tanuj Sharma', 2),
    (105, 'Rajesh', 1),
    (106, 'Gaurav', 4);

CTE查询

-- 定义CTE
WITH EmployeeCTE AS (
    SELECT
        e.EmployeeID,
        e.EmployeeName,
        d.DepartmentName
    FROM
        Employees e
    JOIN
        Departments d ON e.DepartmentID = d.DepartmentID
)

-- 使用CTE选择数据
SELECT *
FROM EmployeeCTE;

递归CTE用于组织结构图

场景

您需要创建一个显示员工层次结构的组织结构图，并计算每个员工在组织中的深度级别。

SQL脚本

-- 创建具有自引用外键的员工表
CREATE TABLE OrgEmployees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    ManagerID INT,
    FOREIGN KEY (ManagerID) REFERENCES OrgEmployees(EmployeeID)
);

-- 插入OrgEmployees样本数据
INSERT INTO OrgEmployees (EmployeeID, EmployeeName, ManagerID) VALUES
    (1, 'CEO', NULL),
    (2, 'CTO', 1),
    (3, 'CFO', 1),
    (4, 'Lead Developer', 2),
    (5, 'Developer', 4),
    (6, 'Lead Accountant', 3),
    (7, 'Accountant', 6);

CTE查询

-- 定义递归CTE
WITH OrgHierarchy AS (
    SELECT
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS DepthLevel,
        CAST(EmployeeName AS VARCHAR(MAX)) AS HierarchyPath
    FROM
        OrgEmployees
    WHERE
        ManagerID IS NULL

    UNION ALL

    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        oh.DepthLevel + 1,
        CAST(oh.HierarchyPath + ' -> ' + e.EmployeeName AS VARCHAR(MAX)) AS HierarchyPath
    FROM
        OrgEmployees e
    JOIN
        OrgHierarchy oh ON e.ManagerID = oh.EmployeeID
)

-- 使用CTE选择组织结构图及其深度级别
SELECT
    EmployeeID,
    EmployeeName,
    ManagerID,
    DepthLevel,
    HierarchyPath
FROM
    OrgHierarchy
ORDER BY
    HierarchyPath;

多级聚合和过滤

场景

您有一个销售数据库，具有多个级别的产品类别，并且想要计算每个类别、子类别和产品的总销售额，然后过滤掉总销售额低于某个阈值的类别。

SQL代码

-- 创建类别表
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(50),
    ParentCategoryID INT NULL
);

-- 创建产品表
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    CategoryID INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

-- 创建销售表
CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductID INT,
    SaleAmount DECIMAL(10, 2),
    SaleDate DATE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- 插入类别样本数据
INSERT INTO Categories (CategoryID, CategoryName, ParentCategoryID) VALUES
    (1, 'Electronics', NULL),
    (2, 'Computers', 1),
    (3, 'Laptops', 2),
    (4, 'Desktops', 2),
    (5, 'Accessories', 1);

-- 插入产品样本数据
INSERT INTO Products (ProductID, ProductName, CategoryID) VALUES
    (1, 'Laptop A', 3),
    (2, 'Laptop B', 3),
    (3, 'Desktop A', 4),
    (4, 'Mouse', 5),
    (5, 'Keyboard', 5);

-- 插入销售样本数据
INSERT INTO Sales (SaleID, ProductID, SaleAmount, SaleDate) VALUES
    (1, 1, 1000.00, '2024-01-01'),
    (2, 2, 1500.00, '2024-01-02'),
    (3, 3, 800.00, '2024-01-03'),
    (4, 4, 20.00, '2024-01-04'),
    (5, 5, 30.00, '2024-01-05');

多级聚合和过滤的CTE

-- 定义产品销售的CTE
WITH ProductSales AS (
    SELECT
        p.CategoryID,
        SUM(s.SaleAmount) AS TotalSales
    FROM
        Sales s
    JOIN
        Products p ON s.ProductID = p.ProductID
    GROUP BY
        p.CategoryID
),

-- 定义类别销售的CTE
CategorySales AS (
    SELECT
        c.CategoryID,
        c.CategoryName,
        c.ParentCategoryID,
        COALESCE(ps.TotalSales, 0) AS TotalSales
    FROM
        Categories c
    LEFT JOIN
        ProductSales ps ON c.CategoryID = ps.CategoryID
),

-- 递归CTE以累积父类别的销售
RecursiveCategorySales AS (
    SELECT
        CategoryID,
        CategoryName,
        ParentCategoryID,
        TotalSales
    FROM
        CategorySales

    UNION ALL

    SELECT
        c.CategoryID,
        c.CategoryName,
        c.ParentCategoryID,
        cs.TotalSales
    FROM
        CategorySales c
    JOIN
        RecursiveCategorySales cs ON c.CategoryID = cs.ParentCategoryID
)

-- 聚合每个类别的总销售额，包括父类别
SELECT
    CategoryID,
    CategoryName,
    SUM(TotalSales) AS TotalSales
FROM
    RecursiveCategorySales
GROUP BY
    CategoryID, CategoryName
HAVING
    SUM(TotalSales) > 500; -- 过滤销售额高于阈值的类别

结论

1. 简化复杂查询：CTEs提供了一种简化复杂SQL查询的方法，通过将查询分解为更小的部分，提高了查询的可读性和可维护性。
2. 提高性能：通过减少重复计算和不必要的数据加载，CTEs可以提高查询性能。
3. 增强灵活性：CTEs允许在查询中引入临时结果集，从而在不修改现有表结构的情况下进行复杂的数据操作。

通过以上示例和解释，可以看出CTEs在SQL中的强大功能和广泛应用。