SQL Server 2017文档权限存储过程：实现用户仅见可访问的最新版本

核心思路 #

先为每个用户确定其在指定文档下拥有权限的最高版本号，随后仅保留该用户与最高版本号对应的权限记录，最后按版本号分组并拼接用户ID列表。这种方式能直接确保每个版本只显示那些未在更高版本中拥有权限的用户。

存储过程代码 #

CREATE PROCEDURE GetLatestAccessibleRevisions
    @DocId INT -- 指定要查询的文档ID
AS
BEGIN
    SET NOCOUNT ON;

    -- 第一步：计算每个用户在目标文档下的最高权限版本号
    WITH UserMaxRevision AS (
        SELECT 
            UserId,
            MAX(RevisionId) AS MaxRevisionId
        FROM DOCUMENT_ACL
        WHERE DocId = @DocId
        GROUP BY UserId
    )
    -- 第二步：筛选出用户权限记录中版本号等于其最高版本的条目，再分组拼接用户ID
    SELECT 
        a.RevisionId,
        STRING_AGG(a.UserId, ',') AS UserIds
    FROM DOCUMENT_ACL a
    INNER JOIN UserMaxRevision umr 
        ON a.UserId = umr.UserId 
        AND a.RevisionId = umr.MaxRevisionId
    WHERE a.DocId = @DocId
    GROUP BY a.RevisionId
    ORDER BY a.RevisionId;
END

代码说明 #

1. CTE UserMaxRevision：通过分组聚合，为每个用户计算出他们在指定文档下能访问的最高版本号，这一步直接排除了用户在低版本的冗余权限记录——我们只需要关心用户可访问的最新版本。
2. 关联筛选：将原权限表与CTE结果关联，仅保留用户权限记录中版本号等于其最高版本的行，自动过滤掉那些在更高版本已有权限的用户在低版本的条目。
3. 分组拼接：利用SQL Server 2017原生支持的STRING_AGG函数，按版本号分组并将用户ID拼接为逗号分隔的字符串，完全匹配需求的输出格式。

测试验证 #

针对你提供的DOCUMENT_ACL表数据（DocId=11），执行以下命令：

EXEC GetLatestAccessibleRevisions @DocId = 11;

将返回预期结果：

内容的提问来源于stack exchange，提问作者user989010