SQL如何筛选状态为Open/Complete的Sub_ID的最早Open_Date？

从指定Orders表中筛选出满足以下条件的最早Open_Date：

• Sub_ID的状态为Open或Complete
• 排除最终状态为Cancelled的Sub_ID

现有表数据 #

当前查询问题 #

当前使用的简单MIN查询会返回2018-07-16，但预期结果应为2022-03-25（对应Sub_ID 002）。当前查询代码：

SELECT 
    MIN (Open_Date) 
FROM 
    (SELECT 
         Order_ID, Sub_ID, Open_Date, Close_Date, Status, Status_Update_Date 
     FROM 
         Orders 
     WHERE 
         Order_ID = 5555) AS X

正确SQL实现 #

核心思路是先确定每个Sub_ID的最终状态，排除最终状态为Cancelled的Sub_ID后，再从剩余Sub_ID中取最早的Open_Date。

方案1：先筛选有效Sub_ID再聚合 #

SELECT MIN(Open_Date)
FROM Orders
WHERE Order_ID = 5555
  AND Sub_ID NOT IN (
      -- 找出所有最终状态为Cancelled的Sub_ID
      SELECT Sub_ID
      FROM (
          SELECT Sub_ID, Status,
                 ROW_NUMBER() OVER (PARTITION BY Sub_ID ORDER BY Status_Update_Date DESC) AS rn
          FROM Orders
          WHERE Order_ID = 5555
      ) t
      WHERE rn = 1 AND Status = 'Cancelled'
  )

方案2：先标记每个Sub_ID的最终状态再筛选 #

WITH SubStatus AS (
    SELECT 
        Sub_ID,
        Status,
        -- 按更新日期倒序，标记每个Sub_ID的最新状态行
        ROW_NUMBER() OVER (PARTITION BY Sub_ID ORDER BY Status_Update_Date DESC) AS rn
    FROM Orders
    WHERE Order_ID = 5555
)
SELECT MIN(o.Open_Date)
FROM SubStatus s
JOIN Orders o ON s.Sub_ID = o.Sub_ID AND o.Order_ID = 5555
WHERE s.rn = 1 
  AND s.Status IN ('Open', 'Complete')

说明 #

• 两个方案都通过ROW_NUMBER()按Status_Update_Date倒序，定位每个Sub_ID的最新状态
• 排除最新状态为Cancelled的Sub_ID后，直接聚合剩余Sub_ID的Open_Date取最小值
• 同一Sub_ID的Open_Date一致，无需额外分组处理

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