Oracle如何查询role表UserId重复记录对应的emp表员工姓名

业务场景 #

现有Oracle数据库包含两张业务表，需提取拥有多个角色的用户对应的emp表姓名字段，预期返回结果为John、May两条记录。

• emp（员工表）：存储员工基础信息，字段为UserId（用户ID）、Name（员工姓名），表内数据如下：

• role（角色表）：存储用户角色关联关系，字段为UserId（用户ID）、Role（角色）；业务规则为同一UserId在表中出现多次即代表对应用户拥有多个角色，表内数据如下：

原有SQL问题 #

原有代码仅能查询role表内重复的UserId，存在两个核心问题：

1. 缺少GROUP BY子句，Oracle语法下分组查询非聚合字段必须声明在GROUP BY后，原SQL直接执行会报语法错误
2. 未关联emp表，无法获取对应的员工姓名
   原问题代码如下：

SELECT UserId, COUNT(*) AS Duplicate
  FROM Role
HAVING COUNT(*)>1

正确实现方案 #

以下两种写法均符合Oracle语法，可返回预期结果：

方案1：分组聚合后关联查询 #

先在role表中分组筛选出拥有多角色的UserId，再内连接emp表匹配姓名，该写法执行效率较高，适合数据量较大的场景：

SELECT e.Name
FROM emp e
JOIN (
    SELECT UserId
    FROM role
    GROUP BY UserId
    HAVING COUNT(*) > 1
) multi_role_user ON e.UserId = multi_role_user.UserId

方案2：EXISTS子查询写法 #

通过EXISTS子句判断当前员工是否存在多角色记录，语法更简洁：

SELECT e.Name
FROM emp e
WHERE EXISTS (
    SELECT 1
    FROM role r
    WHERE r.UserId = e.UserId
    GROUP BY r.UserId
    HAVING COUNT(*) > 1
)

注意：如果表中存在同一用户同一角色重复录入的脏数据，需要将统计逻辑的COUNT(*)替换为COUNT(DISTINCT Role)，避免将重复脏数据误判为多角色。

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