Oracle查询去重user_id关联列 排除Unassigned状态解决ORA-00979

ORA-00979是Oracle使用GROUP BY时的典型语法错误，触发原因是SELECT子句中所有非聚合计算的字段，必须全部列入GROUP BY后的分组字段列表。之前仅按user_id分组，剩余查询字段role_id、role_code、status_code既没有用聚合函数包裹，也没有加入分组字段列表，不符合语法要求直接报错。

另外需求存在隐含逻辑：过滤掉role_code='Unassigned'的无效记录后，同一个user_id仍可能对应多条有效数据（参考给出的测试样例，user_id=4就有role_id=2、3两条符合school_id=5、status_code=DRAFT、role_code非Unassigned的记录），必须先明确每个user_id取哪一条关联记录的规则，才能得到确定的去重结果。以下示例默认采用每个user_id取最小ROLE_ID对应记录的规则编写。

使用Oracle支持的窗口函数ROW_NUMBER()实现按user_id分组排序、取每组第一条的逻辑，不需要写GROUP BY，也不会触发字段不匹配的错误：

SELECT role_id, user_id, role_code, status_code
FROM (
    SELECT 
        role_id, 
        user_id, 
        role_code, 
        status_code,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY role_id ASC) AS rn
    FROM 替换为你的实际表名
    WHERE 
        school_id = 5 
        AND status_code = 'DRAFT'
        AND role_code != 'Unassigned'
) t
WHERE rn = 1;

基于给出的测试样例，执行上述SQL后返回结果如下：

如果需要每个user_id匹配其他规则的记录，仅需要修改窗口函数内的ORDER BY排序逻辑即可，常见调整场景：

• 要取最大ROLE_ID的记录：将排序逻辑改为ORDER BY role_id DESC
• 要取最新创建的记录：将排序逻辑改为ORDER BY create_time DESC（需表中存在对应创建时间字段）
• 要取指定校区优先的记录：可以在排序逻辑中加入campus_id优先级判断，例如ORDER BY CASE WHEN campus_id=7 THEN 1 ELSE 2 END, role_id ASC

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