基于指定优先级规则过滤Oracle视图数据的SQL编写请求

现有视图结构与数据 #

假设现有Oracle视图名为your_view，包含字段id、contractId、opId、opStatus、opDate，数据如下：

过滤优先级规则 #

需按照以下优先级过滤每个contractId下的记录：

• 若同一contractId下存在opStatus为Done的操作，提取所有该状态且opDate相同的记录；
• 若不存在Done状态，提取所有opStatus为Processing且opDate相同的记录；
• 若前两者均不存在，提取该contractId下opDate最早的记录。

预期过滤结果 #

过滤后的结果如下：

解决方案：Oracle SQL语句 #

以下SQL通过预聚合每个contractId的元数据，结合条件筛选实现需求，执行效率较高：

WITH contract_metadata AS (
    SELECT 
        contractId,
        -- 标记当前contractId是否存在Done状态
        MAX(CASE WHEN opStatus = 'Done' THEN 1 ELSE 0 END) AS has_done,
        -- 标记当前contractId是否存在Processing状态
        MAX(CASE WHEN opStatus = 'Processing' THEN 1 ELSE 0 END) AS has_processing,
        -- 获取Done状态的操作日期（所有Done记录日期一致时即为该值）
        MAX(CASE WHEN opStatus = 'Done' THEN opDate END) AS done_date,
        -- 获取Processing状态的操作日期
        MAX(CASE WHEN opStatus = 'Processing' THEN opDate END) AS processing_date,
        -- 获取当前contractId下最早的操作日期
        MIN(opDate) AS earliest_date
    FROM your_view
    GROUP BY contractId
),
view_with_metadata AS (
    SELECT 
        v.*,
        cm.has_done,
        cm.has_processing,
        cm.done_date,
        cm.processing_date,
        cm.earliest_date
    FROM your_view v
    JOIN contract_metadata cm ON v.contractId = cm.contractId
)
SELECT id, contractId, opId, opStatus, opDate
FROM view_with_metadata
WHERE
    -- 优先级1：存在Done状态，筛选Done且日期匹配的记录
    (has_done = 1 AND opStatus = 'Done' AND opDate = done_date)
    -- 优先级2：无Done但有Processing，筛选Processing且日期匹配的记录
    OR (has_done = 0 AND has_processing = 1 AND opStatus = 'Processing' AND opDate = processing_date)
    -- 优先级3：无Done和Processing，筛选最早日期的记录
    OR (has_done = 0 AND has_processing = 0 AND opDate = earliest_date)
ORDER BY contractId, id;

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