Oracle SQL：如何依据ID、TYPE和PART筛选符合条件的数据行

需求说明 #

需要筛选满足以下条件的数据：对于同一ID，若某个PART在TYPE为'A'和'B'的记录中同时存在（即该ID-PART组合至少包含A、B两种类型），则保留这些数据行。

示例数据 #

期望输出 #

基础数据SQL #

WITH data AS (
    SELECT 101 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 20 part FROM dual UNION ALL
    SELECT 101 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 102 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 102 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'A' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'B' type, 25 part FROM dual
)
SELECT * FROM data;

解决方案SQL #

WITH data AS (
    SELECT 101 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 10 part FROM dual UNION ALL
    SELECT 101 id, 'B' type, 20 part FROM dual UNION ALL
    SELECT 101 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 102 id, 'A' type, 10 part FROM dual UNION ALL
    SELECT 102 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'A' type, 25 part FROM dual UNION ALL
    SELECT 103 id, 'B' type, 25 part FROM dual UNION ALL
    SELECT 104 id, 'C' type, 30 part FROM dual UNION ALL
    SELECT 104 id, 'D' type, 30 part FROM dual
), data2 AS (
    SELECT data.*, COUNT(DISTINCT type) OVER (PARTITION BY id, part) cnt
    FROM data
    WHERE type IN ('A','B')
)
SELECT id, type, part
FROM data2
WHERE cnt > 1;

方案逻辑说明 #

1. 先通过WHERE type IN ('A','B')过滤掉非A、B类型的记录，缩小计算范围；
2. 利用窗口函数按id和part分组，统计每组内不同type的数量；
3. 最终筛选出类型数量大于1的记录，即同时包含A和B类型的ID-PART组合的所有行。

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