使用Common Table Expressions (CTE) 简化复杂查询

-- 假设我们有一个Orders表和Customers表
-- 目标：找出过去一年中，购买总金额超过所有客户平均购买总金额的客户，
-- 并列出他们最近一笔订单的详情。

-- 没有CTE，你可能会看到这样的查询，或者更复杂的嵌套：
SELECT
    c.CustomerID,
    c.CustomerName,
    o_latest.OrderID,
    o_latest.OrderDate,
    o_latest.OrderTotal
FROM
    Customers c
JOIN
    (SELECT CustomerID, MAX(OrderDate) AS LatestOrderDate
     FROM Orders
     WHERE OrderDate >= DATEADD(year, -1, GETDATE())
     GROUP BY CustomerID) AS latest_orders_summary
ON c.CustomerID = latest_orders_summary.CustomerID
JOIN
    Orders o_latest ON latest_orders_summary.CustomerID = o_latest.CustomerID
                   AND latest_orders_summary.LatestOrderDate = o_latest.OrderDate
WHERE
    c.CustomerID IN (
        SELECT CustomerID
        FROM (
            SELECT CustomerID, SUM(OrderTotal) AS TotalSpent
            FROM Orders
            WHERE OrderDate >= DATEADD(year, -1, GETDATE())
            GROUP BY CustomerID
        ) AS customer_spending
        WHERE TotalSpent > (
            SELECT AVG(TotalSpent)
            FROM (
                SELECT SUM(OrderTotal) AS TotalSpent
                FROM Orders
                WHERE OrderDate >= DATEADD(year, -1, GETDATE())
                GROUP BY CustomerID
            ) AS all_customer_spending_avg
        )
    );

-- 使用CTE，同样的逻辑变得清晰得多：
WITH RecentOrders AS (
    -- 1. 筛选出过去一年的订单
    SELECT
        CustomerID,
        OrderID,
        OrderDate,
        OrderTotal
    FROM
        Orders
    WHERE
        OrderDate >= DATEADD(year, -1, GETDATE())
),
CustomerSpending AS (
    -- 2. 计算每个客户在过去一年的总消费
    SELECT
        CustomerID,
        SUM(OrderTotal) AS TotalSpent,
        MAX(OrderDate) AS LastOrderDate
    FROM
        RecentOrders
    GROUP BY
        CustomerID
),
AverageOverallSpending AS (
    -- 3. 计算所有客户的平均总消费
    SELECT
        AVG(TotalSpent) AS AvgTotalSpent
    FROM
        CustomerSpending
),
HighValueCustomers AS (
    -- 4. 找出总消费超过平均值的客户
    SELECT
        cs.CustomerID,
        cs.TotalSpent,
        cs.LastOrderDate
    FROM
        CustomerSpending cs
    CROSS JOIN
        AverageOverallSpending aos
    WHERE
        cs.TotalSpent > aos.AvgTotalSpent
)
-- 5. 最后，联结回原始订单和客户信息，获取这些高价值客户的最新订单详情
SELECT
    c.CustomerID,
    c.CustomerName,
    ro.OrderID,
    ro.OrderDate,
    ro.OrderTotal
FROM
    Customers c
JOIN
    HighValueCustomers hvc ON c.CustomerID = hvc.CustomerID
JOIN
    RecentOrders ro ON hvc.CustomerID = ro.CustomerID AND hvc.LastOrderDate = ro.OrderDate;

WITH

SELECT

INSERT

UPDATE

DELETE

CREATE VIEW

UNION ALL

Employees

EmployeeID

EmployeeName

ManagerID

WITH EmployeeHierarchy AS (
    -- 锚定成员：找到某个特定员工（例如，EmployeeID = 101 的员工）
    SELECT
        EmployeeID,
        EmployeeName,
        ManagerID,
        0 AS Level -- 初始层级为0
    FROM
        Employees
    WHERE
        EmployeeID = 101 -- 从这个员工开始向下查找

    UNION ALL

    -- 递归成员：找到上一层级员工的直接下属
    SELECT
        e.EmployeeID,
        e.EmployeeName,
        e.ManagerID,
        eh.Level + 1 AS Level -- 层级加1
    FROM
        Employees e
    JOIN
        EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID -- 联结到CTE自身
)
SELECT
    EmployeeID,
    EmployeeName,
    Level
FROM
    EmployeeHierarchy
ORDER BY
    Level, EmployeeName;