Oracle中如何对两个表执行交集操作且不丢失重复值？

默认的INTERSECT运算符会自动去重，直接用它只能得到A和B各一行，没法保留重复值的出现次数。要实现你需要的效果，这里有几个实用的方案：

方案一：利用行号匹配（兼容Oracle 10g+） #

这个思路是给每个表中相同值的行标记行号，然后只保留两个表中行号和值都匹配的记录——这样就能保留重复值的出现次数（比如两个表中都有2条A，行号1和2都能匹配上，就会返回2条A）。

SELECT a
FROM (
    -- 给TAB1的每行按值分组加行号
    SELECT a, ROW_NUMBER() OVER (PARTITION BY a ORDER BY NULL) AS rn
    FROM tab1
) t1
WHERE EXISTS (
    SELECT 1
    FROM (
        -- 给TAB2的每行按值分组加行号
        SELECT a, ROW_NUMBER() OVER (PARTITION BY a ORDER BY NULL) AS rn
        FROM tab2
    ) t2
    WHERE t2.a = t1.a AND t2.rn = t1.rn
);

执行这个查询后，就能得到你期望的结果：

A
A
B

方案二：统计次数后生成重复行（兼容多数Oracle版本） #

先分别统计两个表中每个值的出现次数，取两者的最小值，再根据这个最小值生成对应数量的行：

WITH tab1_counts AS (
    SELECT a, COUNT(*) AS cnt
    FROM tab1
    GROUP BY a
),
tab2_counts AS (
    SELECT a, COUNT(*) AS cnt
    FROM tab2
    GROUP BY a
),
intersection_counts AS (
    -- 取两个表共有的值，以及该值在两个表中出现次数的最小值
    SELECT t1.a, LEAST(t1.cnt, t2.cnt) AS min_cnt
    FROM tab1_counts t1
    JOIN tab2_counts t2 ON t1.a = t2.a
)
-- 根据min_cnt生成对应次数的行
SELECT ic.a
FROM intersection_counts ic
CONNECT BY ic.a = PRIOR ic.a AND LEVEL <= ic.min_cnt
AND PRIOR SYS_GUID() IS NOT NULL; -- 防止同一值的行产生循环

方案三：用LATERAL简化生成行（Oracle 12c+） #

如果你的Oracle版本是12c及以上，可以用LATERAL关联子查询来简化代码，逻辑和方案二类似，但写法更简洁：

WITH tab1_counts AS (
    SELECT a, COUNT(*) AS cnt
    FROM tab1
    GROUP BY a
),
tab2_counts AS (
    SELECT a, COUNT(*) AS cnt
    FROM tab2
    GROUP BY a
),
intersection_counts AS (
    SELECT t1.a, LEAST(t1.cnt, t2.cnt) AS min_cnt
    FROM tab1_counts t1
    JOIN tab2_counts t2 ON t1.a = t2.a
)
SELECT ic.a
FROM intersection_counts ic,
LATERAL (SELECT 1 FROM dual CONNECT BY LEVEL <= ic.min_cnt);

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