Oracle中UNION ALL合并结果后如何获取重复记录？

针对你的Oracle查询需求，以下是两种添加重复结果检查的实现方式，分别对应合并结果前和合并结果后的场景：

一、合并结果后检查重复 #

这种方式会在两个子查询的结果合并完成后，找出整个结果集中column1和column2重复的记录（重复可能来自同一个子查询，也可能来自两个子查询之间）。

方案1：使用窗口函数标记重复 #

select * from (
    select t.*,
           -- 按column1和column2分组统计出现次数
           count(*) over(partition by column1, column2) as duplicate_count
    from (
        select *
                 from (
                          select column1,column2,rownum as rn from table1 a inner join table2 b on (a.column1=b.column1)) where rn>0
        UNION ALL
        select *
                 from (
                          select column1,column2,rownum as rn from table3 a inner join table4 b on (a.column1=b.column1)) where rn>0
    ) t
) 
-- 筛选出现次数大于1的重复记录
where duplicate_count > 1
and rownum <= 100;

方案2：先分组找出重复键再关联 #

select t.*
from (
    select *
                 from (
                          select column1,column2,rownum as rn from table1 a inner join table2 b on (a.column1=b.column1)) where rn>0
    UNION ALL
    select *
                 from (
                          select column1,column2,rownum as rn from table3 a inner join table4 b on (a.column1=b.column1)) where rn>0
) t
-- 关联预先找出的重复键集合
inner join (
    select column1, column2
    from (
        select column1,column2 from table1 a inner join table2 b on (a.column1=b.column1)
        UNION ALL
        select column1,column2 from table3 a inner join table4 b on (a.column1=b.column1)
    )
    group by column1, column2
    having count(*) > 1
) dup on t.column1 = dup.column1 and t.column2 = dup.column2
where rownum <= 100;

二、合并结果前检查重复 #

这种方式会分别在两个子查询内部先找出column1和column2重复的记录，再将这些重复记录合并，只会统计每个子查询内部的重复，两个子查询之间的重复不会被纳入。

select * from (
    -- 第一个子查询：筛选table1与table2连接后内部的重复记录
    select t.*
    from (
        select column1,column2,rownum as rn,
               count(*) over(partition by column1, column2) as dup_count
        from table1 a inner join table2 b on (a.column1=b.column1)
    ) t
    where dup_count > 1 and rn > 0
    UNION ALL
    -- 第二个子查询：筛选table3与table4连接后内部的重复记录
    select t.*
    from (
        select column1,column2,rownum as rn,
               count(*) over(partition by column1, column2) as dup_count
        from table3 a inner join table4 b on (a.column1=b.column1)
    ) t
    where dup_count > 1 and rn > 0
) where rownum <= 100;

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