SQL Server中按UserRoleId获取唯一记录的查询问题

你的核心问题是多表关联后产生了重复的UserRoleId记录，需要每个UserRoleId仅保留一条，且优先选择权限字段值为1的条目。直接用DISTINCT无法精准筛选优先级，而GROUP BY会因为非聚合字段报错，这里可以用窗口函数ROW_NUMBER()来实现需求：

WITH RankedAccess AS (
    SELECT 
        newFvItems.Id, 
        outerFvia.[UserRoleId], 
        outerFvia.[DefaultStatusId], 
        outerFvia.[CanBeAllocated], 
        outerFvia.[CanCreate], 
        outerFvia.[CanUpdate], 
        outerFvia.[CanDelete], 
        outerFvia.[CanSeeDraft], 
        outerFvia.[CanSeeChecking], 
        outerFvia.[CanSeeCompleted], 
        outerFvia.[CanDispute], 
        outerFvia.[CanResolveDispute], 
        outerFvia.[CanAudit], 
        1 AS FixedValue1, 
        GETUTCDATE() AS CreatedDate, 
        393 AS CreatedById, 
        GETUTCDATE() AS UpdatedDate, 
        393 AS UpdatedById, 
        0 AS IsDeleted, 
        outerFvia.[RecycleBinId], 
        outerFvia.[FlowAccessId],
        -- 按UserRoleId分组，优先排序权限为1的记录
        ROW_NUMBER() OVER (
            PARTITION BY outerFvia.UserRoleId 
            ORDER BY 
                -- 按权限字段优先级排序，可按需调整顺序
                CASE WHEN outerFvia.CanCreate = 1 THEN 0 ELSE 1 END,
                CASE WHEN outerFvia.CanUpdate = 1 THEN 0 ELSE 1 END,
                CASE WHEN outerFvia.CanDelete = 1 THEN 0 ELSE 1 END,
                outerFvia.Id -- 兜底排序，确保分组内唯一
        ) AS RowNum
    FROM FlowVersionItemAccess outerFvia
        JOIN FlowVersionItems outerFvi ON outerFvi.Id = outerFvia.FlowVersionItemId
        JOIN FlowVersions outerFv ON outerFv.Id = outerFvi.FlowVersionId
        JOIN FlowVersionItems newFvItems ON newFvItems.FlowVersionId = 143
    WHERE outerFv.Id = 133
      AND outerFvia.Deleted = 0 
      AND outerFvi.Deleted = 0 
      AND outerFv.Deleted = 0 
)
SELECT 
    Id, UserRoleId, DefaultStatusId, CanBeAllocated, CanCreate, CanUpdate, CanDelete,
    CanSeeDraft, CanSeeChecking, CanSeeCompleted, CanDispute, CanResolveDispute, CanAudit,
    FixedValue1, CreatedDate, CreatedById, UpdatedDate, UpdatedById, IsDeleted,
    RecycleBinId, FlowAccessId
FROM RankedAccess
WHERE RowNum = 1; -- 只保留每个UserRoleId的优先级最高记录

代码说明： #

1. CTE RankedAccess：给每个UserRoleId分组，通过ROW_NUMBER()生成序号。排序规则里用CASE语句把权限为1的记录排到前面，确保优先选中权限生效的条目。
2. 最终筛选：只取RowNum = 1的记录，即每个UserRoleId的唯一优先级最高记录。
3. 若需调整优先判断的权限字段，直接修改ORDER BY里的CASE顺序即可。

为什么不用GROUP BY？ #

你之前遇到的Msg 8120错误，是因为GROUP BY要求所有非聚合字段必须出现在GROUP BY子句里，但newFvItems.Id等字段和UserRoleId是一对多关系，无法直接分组。窗口函数则可以在保留所有字段的同时，实现分组内的排序筛选。

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