如何从两张表中筛选出所有值完全匹配的颜色记录？

嘿，我来帮你搞定这个需求——你要找的是那些在两张表中所有VALUESY值完全一致的颜色：某个颜色在t1里的每一个VALUESY都得在t2里存在，反过来t2里的每一个VALUESY也得在t1里有，而且数量也得完全对应。你现在用的FULL OUTER JOIN会返回所有匹配和不匹配的记录，没法直接筛选出符合要求的颜色，我给你几种实用的实现方法：

方法一：用Oracle的集合比较（最直观） #

Oracle支持直接聚合值为集合，然后对比两个集合是否相等，这种写法最易懂：

WITH t1 as (
    SELECT 'RED' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'RED' as COLOUR, '2' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '2' as VALUESY FROM DUAL
), 
t2 as (
    SELECT 'RED' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'RED' as COLOUR, '3' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '2' as VALUESY FROM DUAL
)
SELECT t1.COLOUR
FROM (
    -- 把每个颜色的VALUESY聚合成集合
    SELECT COLOUR, CAST(COLLECT(VALUESY) AS VARCHAR2_100_TABLE) AS val_set
    FROM t1
    GROUP BY COLOUR
) t1
JOIN (
    SELECT COLOUR, CAST(COLLECT(VALUESY) AS VARCHAR2_100_TABLE) AS val_set
    FROM t2
    GROUP BY COLOUR
) t2 ON t1.COLOUR = t2.COLOUR
-- 直接对比两个集合是否完全相等
WHERE t1.val_set = t2.val_set;

小提示：VARCHAR2_100_TABLE是Oracle内置的字符串表类型，如果你的VALUESY长度不一样，也可以自己定义对应的集合类型。

方法二：用计数+存在性校验（兼容更多场景） #

要是你不想用集合类型，也可以通过统计记录数+校验值的存在性来实现：

WITH t1 as (
    SELECT 'RED' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'RED' as COLOUR, '2' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '2' as VALUESY FROM DUAL
), 
t2 as (
    SELECT 'RED' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'RED' as COLOUR, '3' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '2' as VALUESY FROM DUAL
)
SELECT COLOUR
FROM (
    -- 先过滤出t1里在t2中存在的VALUESY，同时统计每个颜色的总记录数
    SELECT COLOUR, 
           COUNT(*) OVER (PARTITION BY COLOUR) AS t1_total,
           (SELECT COUNT(*) FROM t2 WHERE t2.COLOUR = t1.COLOUR) AS t2_total
    FROM t1
    WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.COLOUR = t1.COLOUR AND t2.VALUESY = t1.VALUESY)
    GROUP BY COLOUR, VALUESY
)
-- 确保两个表的记录数相等，且所有值都匹配（分组后的数量等于总记录数，说明没有遗漏）
GROUP BY COLOUR, t1_total, t2_total
HAVING COUNT(*) = t1_total AND t1_total = t2_total;

方法三：基于你原有的FULL OUTER JOIN改造 #

如果你想基于自己写的FULL OUTER JOIN调整，也可以通过筛选无缺失记录的颜色来实现：

WITH t1 as (
    SELECT 'RED' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'RED' as COLOUR, '2' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '2' as VALUESY FROM DUAL
), 
t2 as (
    SELECT 'RED' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'RED' as COLOUR, '3' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '1' as VALUESY FROM DUAL 
    UNION SELECT 'BLUE' as COLOUR, '2' as VALUESY FROM DUAL
),
full_join_data AS (
    SELECT t1.COLOUR AS t1_colour, t2.COLOUR AS t2_colour,
           t1.VALUESY AS t1_val, t2.VALUESY AS t2_val
    FROM t1 FULL OUTER JOIN t2 
        ON t2.VALUESY = t1.VALUESY AND t2.COLOUR = t1.COLOUR
)
SELECT COALESCE(t1_colour, t2_colour) AS COLOUR
FROM full_join_data
GROUP BY COALESCE(t1_colour, t2_colour)
-- 筛选出没有任何缺失值的颜色：既没有t1为空的记录，也没有t2为空的记录
HAVING SUM(CASE WHEN t1_val IS NULL OR t2_val IS NULL THEN 1 ELSE 0 END) = 0;

这三种方法都能准确返回你需要的BLUE结果，你可以根据自己的Oracle版本和实际数据量来选最合适的写法~

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