SQL如何仅当同组其他列值不同时筛选返回对应重复记录

现有U.Users表包含Name、Role两个字段，测试数据如下：

需求规则：

• 按Name字段分组，分组内存在多个不同Role值时，返回该分组下所有去重后的Name+Role组合
• 分组内所有记录Role值完全一致时，即使该组合有多条重复记录也不返回

期望返回结果：

原尝试写法存在的问题：用ROW_NUMBER()分区排序后筛选行号大于1的记录，只能拿到多角色分组里排序靠后的角色，无法返回该分组下全部角色；关联原表查询时又未做去重和分组角色数判断，会误返回单角色的重复记录。

写法1：窗口函数（推荐，简洁高效） #

直接通过窗口函数计算每个Name下的去重角色数量，一次扫描即可完成计算，不需要额外关联：

WITH cte AS (
    SELECT
        DISTINCT Name,
        Role,
        COUNT(DISTINCT Role) OVER (PARTITION BY Name) AS diff_role_count
    FROM U.Users
)
SELECT Name, Role
FROM cte
WHERE diff_role_count > 1;

逻辑说明：

• 先通过DISTINCT对Name+Role组合去重，避免重复结果
• 窗口函数COUNT(DISTINCT Role) OVER (PARTITION BY Name)会为每个Name分组统计不同角色的总数，不需要提前分组再关联
• 最后筛选不同角色数大于1的记录，刚好匹配需求：既保留多角色用户的所有去重角色，也会过滤掉只有单角色的用户。

写法2：通用兼容写法（适配不支持窗口函数内加DISTINCT的数据库） #

如果使用的数据库版本不支持在窗口函数中使用COUNT(DISTINCT)，可以拆成两步CTE实现，逻辑更直观，兼容性更强：

WITH valid_users AS (
    -- 第一步：筛选出所有拥有多个不同角色的Name
    SELECT Name
    FROM U.Users
    GROUP BY Name
    HAVING COUNT(DISTINCT Role) > 1
),
unique_role_mapping AS (
    -- 第二步：提前对所有Name+Role组合去重
    SELECT DISTINCT Name, Role
    FROM U.Users
)
-- 关联两部分结果，拿到符合要求的记录
SELECT u.Name, u.Role
FROM unique_role_mapping u
JOIN valid_users v ON u.Name = v.Name;

两种写法返回结果完全一致，都可以得到期望的输出。

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