SQL中基于状态时间条件生成Final_Status新列的实现问题

需要在SQL中根据现有数据的Status列和日期顺序，创建名为Final_Status的新列，规则如下：

• 当同一Number的记录中，Canceled或Transferred状态的日期晚于Approved状态的日期时，该Number对应的所有记录的Final_Status分别设为Approved/Canceled或Approved/Transferred
• 其他情况该列留空

当前数据结构 #

尝试的SQL代码（未得到预期结果） #

SELECT a.Number, 
CASE WHEN a.Status = 'Approved' and  a.Status = 'Canceled' or a.Status = 'Transferred' 
THEN
(SELECT MAX(b.Name) 
FROM Internal_Table b 
WHERE a.Number = b.Number
AND b.Status != 'Canceled'
or b.Staus != 'Transfered'
HAVING MAX(b.Date) 
) 
ELSE a.Status END AS Final_Status, 
a.Date,
a.Name 
FROM Internal_Table a

预期结果 #

修正后的SQL代码 #

要实现需求，需先按Number分组统计关键状态的日期关系，再关联回原表生成目标列。以下是兼容多数SQL数据库的写法：

方法1：使用分组CTE关联 #

WITH status_summary AS (
    SELECT 
        Number,
        -- 获取该Number下Approved状态的最新日期
        MAX(CASE WHEN Status = 'Approved' THEN Date END) AS approved_date,
        -- 判断是否存在晚于Approved日期的Canceled
        CASE WHEN EXISTS (
            SELECT 1 FROM Internal_Table b
            WHERE b.Number = it.Number
              AND b.Status = 'Canceled'
              AND b.Date > MAX(CASE WHEN it.Status = 'Approved' THEN it.Date END)
        ) THEN 'Canceled' END AS has_late_canceled,
        -- 判断是否存在晚于Approved日期的Transferred
        CASE WHEN EXISTS (
            SELECT 1 FROM Internal_Table b
            WHERE b.Number = it.Number
              AND b.Status = 'Transferred'
              AND b.Date > MAX(CASE WHEN it.Status = 'Approved' THEN it.Date END)
        ) THEN 'Transferred' END AS has_late_transferred
    FROM Internal_Table it
    GROUP BY Number
)
SELECT 
    it.Number,
    it.Name,
    it.Date,
    it.Status,
    CASE
        WHEN ss.has_late_canceled IS NOT NULL THEN 'Approved/Canceled'
        WHEN ss.has_late_transferred IS NOT NULL THEN 'Approved/Transferred'
        ELSE ''
    END AS Final_Status
FROM Internal_Table it
LEFT JOIN status_summary ss ON it.Number = ss.Number
ORDER BY it.Number, it.Date;

方法2：使用窗口函数（更简洁） #

如果你的数据库支持窗口函数（如MySQL 8.0+、PostgreSQL、SQL Server等），可以用以下写法：

WITH number_statuses AS (
    SELECT 
        *,
        -- 获取当前Number下Approved的最新日期
        MAX(CASE WHEN Status = 'Approved' THEN Date END) OVER (PARTITION BY Number) AS max_approved_date,
        -- 标记当前Number是否有晚于Approved的Canceled
        MAX(CASE WHEN Status = 'Canceled' AND Date > MAX(CASE WHEN Status = 'Approved' THEN Date END) OVER (PARTITION BY Number) THEN 1 ELSE 0 END) OVER (PARTITION BY Number) AS has_late_canceled,
        -- 标记当前Number是否有晚于Approved的Transferred
        MAX(CASE WHEN Status = 'Transferred' AND Date > MAX(CASE WHEN Status = 'Approved' THEN Date END) OVER (PARTITION BY Number) THEN 1 ELSE 0 END) OVER (PARTITION BY Number) AS has_late_transferred
    FROM Internal_Table
)
SELECT 
    Number,
    Name,
    Date,
    Status,
    CASE
        WHEN has_late_canceled = 1 THEN 'Approved/Canceled'
        WHEN has_late_transferred = 1 THEN 'Approved/Transferred'
        ELSE ''
    END AS Final_Status
FROM number_statuses
ORDER BY Number, Date;

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