Oracle中判断两个结果集是否完全一致的其他实现方法

由于MINUS操作会自动去重，无法准确对比包含重复记录的结果集。除了你提供的INTERSECT统计方法，还有以下几种可行方案：

方案一：分组统计+全外连接 #

核心思路是对两个结果集按所有列分组，统计每个分组的行数，再通过全外连接对比分组和行数是否完全匹配。

WITH set_a AS (
    SELECT 1 x FROM dual UNION ALL
    SELECT 2 x FROM dual UNION ALL
    SELECT 1 x FROM dual
),
set_b AS (
    SELECT 1 x FROM dual UNION ALL
    SELECT 2 x FROM dual
),
a_grouped AS (
    SELECT x, COUNT(*) AS cnt FROM set_a GROUP BY x
),
b_grouped AS (
    SELECT x, COUNT(*) AS cnt FROM set_b GROUP BY x
)
SELECT 
    COALESCE(a.x, b.x) AS x,
    COALESCE(a.cnt, 0) AS a_cnt,
    COALESCE(b.cnt, 0) AS b_cnt,
    CASE 
        WHEN a.cnt = b.cnt THEN '匹配'
        ELSE '不匹配'
    END AS match_status
FROM a_grouped a
FULL OUTER JOIN b_grouped b ON a.x = b.x
WHERE a.cnt != b.cnt OR a.x IS NULL OR b.x IS NULL;

• 如果查询返回空结果，说明两个结果集完全一致；
• 如果有返回行，说明存在值不匹配或重复次数不同的分组。

方案二：行号标识+全外连接 #

通过ROW_NUMBER()给每个重复行生成唯一标识（按列分组后排序加行号），让每一行都有唯一的区分依据，再对比所有行。

WITH set_a AS (
    SELECT 1 x FROM dual UNION ALL
    SELECT 2 x FROM dual UNION ALL
    SELECT 1 x FROM dual
),
set_b AS (
    SELECT 1 x FROM dual UNION ALL
    SELECT 2 x FROM dual
),
a_rn AS (
    SELECT x, ROW_NUMBER() OVER (PARTITION BY x ORDER BY NULL) AS rn FROM set_a
),
b_rn AS (
    SELECT x, ROW_NUMBER() OVER (PARTITION BY x ORDER BY NULL) AS rn FROM set_b
)
SELECT 
    COALESCE(a.x, b.x) AS x,
    COALESCE(a.rn, 0) AS a_rn,
    COALESCE(b.rn, 0) AS b_rn,
    CASE 
        WHEN a.x = b.x AND a.rn = b.rn THEN '匹配'
        ELSE '不匹配'
    END AS match_status
FROM a_rn a
FULL OUTER JOIN b_rn b ON a.x = b.x AND a.rn = b.rn
WHERE a.x IS NULL OR b.x IS NULL;

• 空结果表示两个结果集的每一行（包括重复行）都完全匹配；
• 返回行则说明存在缺失或多余的重复行。

方案三：对称差集统计 #

将两个结果集合并后按值统计来源次数，找出重复次数差异，再补充仅存在于单一集合的值。

WITH set_a AS (
    SELECT 1 x FROM dual UNION ALL
    SELECT 2 x FROM dual UNION ALL
    SELECT 1 x FROM dual
),
set_b AS (
    SELECT 1 x FROM dual UNION ALL
    SELECT 2 x FROM dual
),
combined AS (
    SELECT x, 'A' AS source FROM set_a
    UNION ALL
    SELECT x, 'B' AS source FROM set_b
),
diff_counts AS (
    SELECT x, 
           COUNT(CASE WHEN source = 'A' THEN 1 END) AS a_cnt,
           COUNT(CASE WHEN source = 'B' THEN 1 END) AS b_cnt
    FROM combined
    GROUP BY x
)
SELECT x, a_cnt, b_cnt, '重复次数不匹配' AS diff_type
FROM diff_counts
WHERE a_cnt != b_cnt
UNION ALL
SELECT x, 0, 0, '仅存在于A' AS diff_type
FROM set_a
WHERE x NOT IN (SELECT x FROM set_b)
UNION ALL
SELECT x, 0, 0, '仅存在于B' AS diff_type
FROM set_b
WHERE x NOT IN (SELECT x FROM set_a);

• 空结果表示两个结果集完全一致；
• 返回行会明确标出差异类型：重复次数不同、仅存在于A或仅存在于B。

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