Oracle SQL查询：按NAMEUNIQUE分组筛选符合CREATOR_TYPE条件的SCHEDULES数据

表结构说明 #

现有Oracle数据库中的SCHEDULES表包含以下字段：

• ID
• NAMEUNIQUE
• STARTTIME
• ENDTIME
• STATUS
• CREATOR
• CREATOR_TYPE（可取值为1、2、3）

查询规则要求 #

1. 按NAMEUNIQUE分组后，若分组内所有记录的CREATOR_TYPE都仅为1，不存在值为2或3的记录，则过滤该分组下的全部记录
2. 其余所有分组的全部记录都保留

样例参考 #

样例数据 #

预期结果 #

返回ID为2、3、4、5的记录，仅过滤ID为1的记录，原因是AAAAAAAAAA分组下仅存在CREATOR_TYPE=1的记录，无2或3的记录。

可用SQL实现 #

写法1：窗口函数实现（Oracle 11g及以上版本支持，性能更优） #

SELECT ID, NAMEUNIQUE, STARTTIME, ENDTIME, STATUS, CREATOR, CREATOR_TYPE
FROM (
    SELECT 
        t.*,
        MAX(CASE WHEN CREATOR_TYPE IN (2,3) THEN 1 ELSE 0 END) OVER (PARTITION BY NAMEUNIQUE) AS has_valid_type
    FROM SCHEDULES t
)
WHERE has_valid_type = 1;

写法2：EXISTS子查询实现（兼容所有Oracle版本） #

SELECT t.*
FROM SCHEDULES t
WHERE EXISTS (
    SELECT 1 
    FROM SCHEDULES t2 
    WHERE t2.NAMEUNIQUE = t.NAMEUNIQUE 
    AND t2.CREATOR_TYPE IN (2,3)
);

逻辑说明 #

两种写法的核心逻辑都是判断每个NAMEUNIQUE分组下是否存在CREATOR_TYPE为2或3的记录，存在则保留该分组全部记录，否则全部过滤，完全匹配要求的规则。

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