使用ROW_NUMBER解析函数，以便在每个集合中有唯一的行，然后FULL OUTER JOIN：

WITH set_a (x) AS (
  select 1 from dual union all -- Some silly result set with some duplicate data
  select 2 from dual union all
  select 1 from dual
), 
set_b (x) AS (
  select 1 from dual union all -- Another silly result set to compare against
  select 2 from dual 
),
unique_a (x, rn) AS (
  SELECT x, ROW_NUMBER() OVER (PARTITION BY x ORDER BY ROWNUM) FROM set_a
),
unique_b (x, rn) AS (
  SELECT x, ROW_NUMBER() OVER (PARTITION BY x ORDER BY ROWNUM) FROM set_b
)
SELECT COUNT(CASE WHEN a.rn = b.rn THEN 1 END) AS num_identical_in_a_and_b,
       COUNT(CASE WHEN a.rn IS NULL THEN 1 END) AS num_not_identical_in_a,
       COUNT(CASE WHEN b.rn IS NULL THEN 1 END) AS num_not_identical_in_b
FROM   unique_a a
       FULL OUTER JOIN unique_b b
       ON (a.x = b.x AND a.rn = b.rn)

字符串
哪些输出：
| NUM_NOT_IDENTICAL_IN_A| NUM_NOT_IDENTICAL_IN_B| NUM_NOT_IDENTICAL_IN_B |
| --|--| ------------ |
| 0个|一个| 1 |
fiddle

一个简单的解决方案，我在过去使用：

• 计算a except all b

这将为您提供a中所有不在B中的项目...但这还不够！

• 计算b except all a

这会给你b中所有不在a中的项目

• union all两个结果

如果最终结果为空，则两个集合相等
这种方法比较所有行的所有列;您甚至可以将行(1, 'foo', 'bar')与行(1, 'foo', 'baz)进行比较。

select * from (
    select * from a
    except all
    select * from b
) a_only

union all

select * from (
    select * from b
    except all
    select * from a
) b_only

字符串
DB<>Fiddle

WITH set_a AS
(
    SELECT 1 AS ID, 'John' AS Name FROM dual UNION ALL
    SELECT 2 AS ID, 'Jane' AS Name FROM dual UNION ALL
    SELECT 3 AS ID, 'Mike' AS Name FROM dual UNION ALL
    SELECT 4 AS ID, 'Sarah' AS Name FROM dual
),
set_b AS
(
    SELECT 1 AS ID, 'John' AS Name FROM dual UNION ALL
    SELECT 2 AS ID, 'Jane' AS Name FROM dual UNION ALL
    SELECT 3 AS ID, 'Mike' AS Name FROM dual
)
SELECT 
    (SELECT COUNT(*) FROM set_a) AS a_count,
    (SELECT COUNT(*) FROM set_b) AS b_count,
    CASE 
        WHEN (SELECT COUNT(*) FROM set_a) = (SELECT COUNT(*) FROM set_b) THEN 'Good'
        ELSE 'Bad'
    END AS result
FROM dual;

字符串
| B_COUNT|结果| RESULT |
| --|--| ------------ |
| 三个|坏了| Bad |

WITH set_a AS
(
    SELECT 1 AS ID, 'Apple' AS Fruit FROM dual UNION ALL
    SELECT 2 AS ID, 'Banana' AS Fruit FROM dual UNION ALL
    SELECT 3 AS ID, 'Orange' AS Fruit FROM dual
),
set_b AS
(
    SELECT 1 AS ID, 'Apple' AS Fruit FROM dual UNION ALL
    SELECT 2 AS ID, 'Banana' AS Fruit FROM dual UNION ALL
    SELECT 3 AS ID, 'Orange' AS Fruit FROM dual UNION ALL
    SELECT 4 AS ID, 'Kiwi' AS Fruit FROM dual
)
SELECT 
    (SELECT COUNT(*) FROM set_a) AS a_count,
    (SELECT COUNT(*) FROM set_b) AS b_count,
    CASE 
        WHEN (SELECT COUNT(*) FROM set_a) = (SELECT COUNT(*) FROM set_b) THEN 'Good'
        ELSE 'Bad'
    END AS result
FROM dual;

型
| B_COUNT|结果| RESULT |
| --|--| ------------ |
| 四个|坏了| Bad |

WITH set_a AS
(
    SELECT 1 AS ID, 'New York' AS City FROM dual UNION ALL
    SELECT 2 AS ID, 'Los Angeles' AS City FROM dual UNION ALL
    SELECT 3 AS ID, 'Chicago' AS City FROM dual UNION ALL
    SELECT 4 AS ID, 'Houston' AS City FROM dual
),
set_b AS
(
    SELECT 1 AS ID, 'New York' AS City FROM dual UNION ALL
    SELECT 2 AS ID, 'Los Angeles' AS City FROM dual UNION ALL
    SELECT 3 AS ID, 'Chicago' AS City FROM dual UNION ALL
    SELECT 4 AS ID, 'Houston' AS City FROM dual
)
SELECT 
    (SELECT COUNT(*) FROM set_a) AS a_count,
    (SELECT COUNT(*) FROM set_b) AS b_count,
    CASE 
        WHEN (SELECT COUNT(*) FROM set_a) = (SELECT COUNT(*) FROM set_b) THEN 'Good'
        ELSE 'Bad'
    END AS result
FROM dual;

型
| B_COUNT|结果| RESULT |
| --|--| ------------ |
| 四个|很好，很好| Good |
fiddle

从Oracle Database 21c开始，您可以使用MINUS ALL，这正是您所需要的（即减去 * 而不进行重复数据删除 *）。

select * from set_a
MINUS ALL
select * from set_b
;

         X
----------
         1

select * from set_b
MINUS ALL
select * from set_a
;
no rows selected

字符串
如果 both 查询返回 no rows，则行源 * 相同 *。
是的，同样从21c开始，你可以使用标准关键字EXCEPT ALL来代替 * 非标准 * MINUS ALL。