sql-server公用表表达式

视图是作为数据库对象存储在数据库中的，如果这个结果集仅仅要使用一次，那么建立视图就太奢侈了。

在SQL Server中，公共表表达式（Common Table Expression，简称CTE）是一种临时的结果集，可以在一个查询块中多次引用。CTE可以用来简化复杂的查询，特别是那些需要多步操作或者递归查询的情况。下面是一个详细的介绍，包括CTE的语法和使用示例。

WITH 公用表名 [(自定列名表)] AS
(SELECT...)

注意，公用表表达式只能且必须在后面的一个SELECT/INSERT/UPDATE/DELETE/MERGE语句中使用，但这条语句未结束时可以多次使用，结束后就失效了。

WITH CTE_Name (Column1, Column2, ...) AS (
    -- CTE 的定义
    SELECT ...
    FROM ...
    WHERE ...
    GROUP BY ...
    HAVING ...
    ORDER BY ...
)
-- 正常的 SQL 查询
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...

示例 1: 简单的CTE

假设我们有一个包含用户信息的表Users，并且想要创建一个CTE来计算每个用户的积分总和。

WITH UserPoints AS (
    SELECT
        U.Id,
        SUM(P.Points) AS TotalPoints
    FROM
        Users U
    JOIN
        PointsTransactions P ON U.Id = P.UserId
    GROUP BY
        U.Id
)
SELECT
    U.Username,
    UP.TotalPoints
FROM
    Users U
JOIN
    UserPoints UP ON U.Id = UP.Id
ORDER BY
    UP.TotalPoints DESC;

示例 2: 使用多个CTE

WITH PointType AS (
    SELECT Id
    FROM PointTypes
    WHERE TypeName = '购物积分'
),
UserPoints AS (
    SELECT
        U.Id AS UserId,
        SUM(PT.Points) AS TotalPoints
    FROM
        Users U
    JOIN
        PointsTransactions PT ON U.Id = PT.UserId
    JOIN
        PointType PTy ON PT.PointTypeId = PTy.Id
    GROUP BY
        U.Id
)
SELECT
    U.Username,
    UP.TotalPoints
FROM
    Users U
JOIN
    UserPoints UP ON U.Id = UP.UserId
ORDER BY
    UP.TotalPoints DESC;

PointType CTE:获取“购物积分”类型的ID。
UserPoints CTE:计算每个用户在“购物积分”类型的积分总和。
最终查询:将用户表与UserPoints CTE连接，并按积分总和降序排列。

示例 3: 递归CTE

WITH InitialPoints AS (
    SELECT
        U.Id AS UserId,
        PH.TransactionDate AS TransactionDate,
        PH.PointsBefore AS Points
    FROM
        Users U
    JOIN
        PointsHistory PH ON U.Id = PH.UserId
    WHERE
        PH.TransactionDate = (
            SELECT MIN(TransactionDate)
            FROM PointsHistory
            WHERE UserId = U.Id
        )
),
RecursivePoints AS (
    SELECT
        U.Id AS UserId,
        PH.TransactionDate AS TransactionDate,
        PH.PointsAfter AS Points
    FROM
        Users U
    JOIN
        PointsHistory PH ON U.Id = PH.UserId
    WHERE
        PH.TransactionDate > (
            SELECT TransactionDate
            FROM InitialPoints I
            WHERE I.UserId = U.Id
        )
)
SELECT
    U.Username,
    IP.TransactionDate,
    IP.Points
FROM
    Users U
JOIN
    InitialPoints IP ON U.Id = IP.UserId
UNION ALL
SELECT
    U.Username,
    RP.TransactionDate,
    RP.Points
FROM
    Users U
JOIN
    RecursivePoints RP ON U.Id = RP.UserId
ORDER BY
    U.Username, IP.TransactionDate;

InitialPoints CTE:查找每个用户的首次积分变动记录，并记录当时的积分余额。
RecursivePoints CTE:查找每个用户之后的所有积分变动记录，直到没有更多的记录为止。
最终查询:将用户表与InitialPoints CTE结合，并按用户名和交易日期排序输出结果。同时，也将递归部分的结果合并进来。

注意：递归CTE必须有一个明确的终止条件，否则可能会导致无限循环。

这种递归CTE的使用方式非常适合处理具有层次结构的数据，比如组织架构、文件系统路径等。通过递归CTE，可以方便地查询和展示出整个层次结构。