如何在Oracle中查找完全重复的行与字符串？寻求更优雅方案

方法1：用窗口函数获取所有重复行（更直观灵活） #

相比原始GROUP BY仅统计次数，窗口函数可以直接返回所有重复的完整行记录，方便后续处理：

SELECT t.*
FROM (
    SELECT 
        tab.*,
        -- 按目标字段分组，给每组行编号
        ROW_NUMBER() OVER(PARTITION BY 1col, 2col, 3col ORDER BY 1) AS row_num
    FROM tab
) t
WHERE t.row_num > 1;

如果需要保留重复组的所有行（包括第一行），可以改用COUNT窗口函数：

SELECT t.*
FROM (
    SELECT 
        tab.*,
        COUNT(*) OVER(PARTITION BY 1col, 2col, 3col) AS duplicate_count
    FROM tab
) t
WHERE t.duplicate_count > 1;

方法2：HASH分组优化大表查询性能 #

针对数据量较大的表，利用Oracle的哈希分组可以提升效率，通过哈希值快速识别重复组：

SELECT 
    1col, 2col, 3col,
    COUNT(*) AS duplicate_count,
    -- 生成组合字段的哈希值辅助分组
    ORA_HASH(NVL(1col, '') || '|' || NVL(2col, '') || '|' || NVL(3col, '')) AS row_hash
FROM tab
GROUP BY 1col, 2col, 3col, ORA_HASH(NVL(1col, '') || '|' || NVL(2col, '') || '|' || NVL(3col, ''))
HAVING COUNT(*) > 1;

注：用NVL处理NULL值，加分隔符|避免不同字段拼接产生歧义。

方法3：查找多字段拼接后的重复字符串 #

如果需要检查多个字段拼接成的字符串是否重复，可直接对拼接结果分组或结合窗口函数：

-- 统计重复的拼接字符串及次数
SELECT 
    NVL(1col, '') || '|' || NVL(2col, '') || '|' || NVL(3col, '') AS combined_str,
    COUNT(*) AS duplicate_count
FROM tab
GROUP BY NVL(1col, '') || '|' || NVL(2col, '') || '|' || NVL(3col, '')
HAVING COUNT(*) > 1;

-- 返回所有包含重复拼接字符串的行
SELECT t.*
FROM (
    SELECT 
        tab.*,
        COUNT(*) OVER(PARTITION BY NVL(1col, '') || '|' || NVL(2col, '') || '|' || NVL(3col, '')) AS cnt
    FROM tab
) t
WHERE t.cnt > 1;

方案对比 #

• 窗口函数方案：适合需要查看完整重复行的场景，无需二次关联，扩展性强；
• HASH分组方案：针对大表优化性能，减少分组时的内存消耗；
• 原始GROUP BY方案：仅适合统计重复组和次数的简单场景，灵活性不足。

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