Oracle中避免Union子查询重复ID的更优雅实现方案问询

针对你遇到的问题，不需要重复复制前两个子查询的代码到第三个子查询的NOT IN中，Oracle提供了几种更简洁高效的实现方式：

方案1：使用CTE（公共表表达式）复用前两个子查询结果 #

通过CTE先将前两个子查询的结果统一存储，再让第三个子查询排除这些ID，最后用UNION ALL合并结果（因为已提前排除重复，UNION ALL比UNION效率更高）：

WITH first_two_queries AS (
    -- 原unionquery1的完整代码
    (... subquery expression with some ids)
    UNION
    -- 原unionquery2的完整代码
    (... another subquery expression with some ids)
)
-- 先取前两个查询的所有结果
SELECT * FROM first_two_queries
UNION ALL
-- 取第三个查询中与前两个无重复的结果
SELECT * FROM (... another subquery expression with some ids)
WHERE id NOT IN (SELECT id FROM first_two_queries);

方案2：用窗口函数控制去重优先级 #

如果需要更精细地控制重复ID的保留规则（比如优先保留前两个子查询的记录，过滤第三个的重复项），可以使用ROW_NUMBER()窗口函数：

SELECT col1, col2, ..., id  -- 替换为你实际需要的字段，不建议用*
FROM (
    SELECT 
        t.*,
        -- 按ID分组，前两个子查询的记录优先级设为1，第三个设为2
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY priority) AS rn
    FROM (
        SELECT *, 1 AS priority FROM (... subquery expression with some ids)
        UNION ALL
        SELECT *, 1 AS priority FROM (... another subquery expression with some ids)
        UNION ALL
        SELECT *, 2 AS priority FROM (... another subquery expression with some ids)
    ) t
)
-- 只保留每个ID的最高优先级记录
WHERE rn = 1;

这个方案还能处理前两个子查询之间可能存在的重复ID，同时避免UNION带来的全局去重开销。

补充说明 #

如果你的原查询使用的是UNION而非UNION ALL，理论上UNION会自动去重所有重复记录，但如果出现重复ID，大概率是因为除ID外的其他字段值不同，此时需要明确去重规则（比如保留哪部分的字段值），方案2能很好地解决这类场景。

以上两种方案Oracle均支持（Oracle 11g及以上版本完全兼容CTE和窗口函数），且代码复用性更强，后期维护更方便。

内容的提问来源于stack exchange，提问作者D. Rattansingh