如何解决Oracle查询返回重复结果问题（GROUP BY失效）

原查询的核心问题 #

1. GROUP BY字段冗余：你把cons_pk.table_name、cols_pk.column_name、cons_pk.CONSTRAINT_TYPE都加入GROUP BY，这些字段如果同一列对应多个不同值（比如一个列关联了多个约束记录），会直接把同一个COLUMNNAME拆分成多个分组，导致结果重复。
2. JOIN逻辑错误：最后一个LEFT JOIN all_constraints cons_pk的条件cons_pk.table_name=cols.table_name会把当前表的所有约束都关联进来，而非仅关联外键对应的主键约束，这会引入大量无关的重复数据。
3. 聚合逻辑不匹配需求：只有PK字段用了聚合函数，其他关联字段直接放到GROUP BY里，完全没实现“每个列仅出现一次”的目标。

修正后的查询语句 #

SELECT
    cols.column_name AS COLUMNNAME,
    cols.data_type AS COLUMNDATATYPE,
    cols.data_length AS COLUMNDATALENGTH,
    MAX(CASE WHEN cons.constraint_type = 'P' THEN 'Y' ELSE 'N' END) AS PK,
    MAX(cons_pk.table_name) AS REFPROCNAME,
    MAX(cols_pk.column_name) AS COLUMNVALUEFIELD,
    MAX(CASE WHEN cons.constraint_type = 'R' THEN 1 ELSE 0 END) AS ISCHOOSABLE
FROM all_tab_columns cols
LEFT JOIN all_cons_columns cols_pk 
    ON cols.owner = cols_pk.owner
    AND cols.table_name = cols_pk.table_name
    AND cols.column_name = cols_pk.column_name
LEFT JOIN all_constraints cons 
    ON cols_pk.owner = cons.owner
    AND cols_pk.constraint_name = cons.constraint_name
LEFT JOIN all_constraints cons_pk 
    ON cons.r_owner = cons_pk.owner
    AND cons.r_constraint_name = cons_pk.constraint_name
WHERE cols.owner = 'AO' AND cols.table_name = 'MACHINEPRINTER'
GROUP BY cols.column_name, cols.data_type, cols.data_length

关键调整说明 #

• 精简GROUP BY：只保留列的唯一标识字段，确保同一列只会生成一条结果记录。
• 修正外键关联：把错误的cons_pk.table_name=cols.table_name改成cons.r_constraint_name = cons_pk.constraint_name，仅关联当前外键对应的主键约束，避免无关数据混入。
• 统一聚合处理：所有非GROUP BY字段用MAX聚合（如果一个列对应多个关联值，MAX会取其中一个；若需保留所有值，可替换为LISTAGG(字段名, ',') WITHIN GROUP (ORDER BY 字段名)来拼接）。
• 优化PK标记逻辑：用CASE语句更直观地标记是否为主键，替代原语句返回约束类型的逻辑。

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