Oracle SQL 筛选同时含指定类型的订单关联记录及优化方法

你现有方案跑不通的核心原因 #

• 字段名错配：这是原方案无法执行的最直接原因，CTE子查询里取的是ID字段，但你表X里的订单标识列实际名称是Order，执行时会直接抛出字段不存在的语法错误，根本无法产出结果
• 扩展性差：如果后续要校验的标记类型从2种增加到3种、4种，每加一种就要多写一个独立CTE，维护成本很高
• 执行效率低：多次重复扫描同一张表X，数据量上来之后会产生不必要的IO开销

更优的可落地实现方式 #

通用聚合校验方案（适配任意数量的必选类型） #

核心逻辑是先统计每个订单命中必选类型的数量，只要数量和必选类型总数一致，就说明这个订单符合要求，最后把这类订单的所有关联行全部拉取出来：

WITH qualified_orders AS (
    -- 把需要校验的必选类型都放在IN列表里
    SELECT DISTINCT `Order`
    FROM X
    WHERE Type IN ('A', 'B')
    GROUP BY `Order`
    -- 统计到的不同必选类型数量和总数一致，即符合筛选要求
    HAVING COUNT(DISTINCT Type) = 2
)
SELECT x.*
FROM X x
JOIN qualified_orders q ON x.`Order` = q.`Order`;

• 后续要调整必选类型时，只需要修改IN后面的类型列表，同时把HAVING后的数值改成对应类型的总个数即可，不需要重复写多个CTE
• 仅需两次扫描表X，比原方案多次扫表的执行效率更高

单表扫描的窗口函数方案（大表场景性能最优） #

通过窗口函数直接给每一行打筛选标记，不需要多表关联，单遍扫描即可完成校验：

SELECT `Order`, Type
FROM (
    SELECT 
        `Order`,
        Type,
        -- 统计当前订单分区内，命中必选类型的去重数量
        COUNT(DISTINCT CASE WHEN Type IN ('A','B') THEN Type END) OVER (PARTITION BY `Order`) AS hit_type_cnt
    FROM X
) t
WHERE hit_type_cnt = 2;

• 该方案仅需一次全表扫描，亿级大表场景下性能优势明显
• 同样可以灵活调整命中规则，适配不同的必选类型组合要求

原逻辑修正版（不想改动现有逻辑时用） #

只要把CTE里写错的ID字段替换成实际的订单列名Order，就能跑出正确结果：

WITH FIRST AS (
    SELECT DISTINCT `Order`
    FROM X 
    WHERE Type = 'A'
),
SECOND AS (
    SELECT DISTINCT `Order`
    FROM X 
    WHERE Type = 'B'
)
SELECT *
FROM X 
WHERE `Order` IN (
    SELECT f.`Order`
    FROM FIRST f
    JOIN SECOND s ON f.`Order` = s.`Order`
);

三种实现方式的输出结果和预期完全一致，只会保留123、567两个同时包含A、B类型的订单的所有关联行，不会把仅含单一类型的订单（如234、345、456）纳入结果集。

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