Oracle SQL Developer：按Condition与Date规则筛选ItemNo的查询需求

需求说明 #

需要编写Oracle SQL查询，返回符合特定规则的ItemNo。单个ItemNo可能对应多个Condition，需基于关联条件过滤结果。

数据集 #

过滤规则 #

仅返回不存在以下情况的ItemNo：当Condition为B时，其Date不等于'9999-99-99' 或 晚于同ItemNo的Condition A的Date。

• 示例1：ItemNo 1234不应被返回，因其Condition B的Date与A相同且为'9999-99-99'（触发排除规则）
• 示例2：ItemNo 5555应被返回，因其Condition B的Date早于A（未触发排除规则）

当前使用的语句（不符合需求） #

select ItemNo from DataSet where Condition not in 'B' and Date between :a and :b

解决方案 #

可以通过子查询或分组聚合的方式实现规则校验，以下是两种可行写法：

方法1：使用NOT EXISTS子查询排除违规ItemNo #

SELECT DISTINCT ItemNo
FROM DataSet d
WHERE NOT EXISTS (
    SELECT 1
    FROM DataSet b
    JOIN DataSet a ON b.ItemNo = a.ItemNo AND a.Condition = 'A'
    WHERE b.ItemNo = d.ItemNo
      AND b.Condition = 'B'
      AND (b.Date <> '9999-99-99' OR b.Date > a.Date)
)

方法2：分组聚合后过滤 #

如果每个ItemNo仅对应一条Condition A的记录（或取最新的A日期），可以用分组+HAVING子句实现：

SELECT ItemNo
FROM (
    SELECT 
        ItemNo,
        MAX(CASE WHEN Condition = 'A' THEN Date END) AS A_Date,
        MAX(CASE WHEN Condition = 'B' THEN Date END) AS B_Date
    FROM DataSet
    GROUP BY ItemNo
) t
WHERE NOT (B_Date <> '9999-99-99' OR B_Date > A_Date)

逻辑说明 #

• 方法1通过NOT EXISTS子查询校验：若当前ItemNo存在满足排除规则的B记录（日期非'9999-99-99'或晚于同ItemNo的A日期），则排除该ItemNo。
• 方法2先分组提取每个ItemNo的A、B日期（用MAX聚合，可根据实际数据调整聚合逻辑），再在外层过滤掉触发排除规则的记录。

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