如何从History表筛选完成后变更状态的表单及首次变更信息

需求回顾 #

从History表（字段：FormId、Status、Date）中筛选出：

• 曾处于Completed状态
• 完成后有状态变更的表单
  返回字段：FormId、完成日期（CompleteDate）、完成后的首次变更状态、首次变更日期（FirstUpdateDate）

示例数据 #

期望结果 #

问题分析 #

你之前的SQL错误在于用max(ChangeDate) over (partition by h.formId)获取了表单的最后更新日期，而非完成日期之后的首次变更日期。需要调整逻辑，聚焦在完成时间点之后的第一条状态记录。

解决方案SQL #

WITH FormComplete AS (
    -- 获取每个表单的首次完成日期
    SELECT 
        FormId,
        MIN(Date) AS CompleteDate
    FROM History
    WHERE Status = 'Completed'
    GROUP BY FormId
),
PostCompleteChanges AS (
    -- 标记每个表单完成后的首次变更记录
    SELECT 
        h.FormId,
        h.Status,
        h.Date AS FirstUpdateDate,
        ROW_NUMBER() OVER (PARTITION BY h.FormId ORDER BY h.Date ASC) AS rn
    FROM History h
    JOIN FormComplete fc ON h.FormId = fc.FormId
    WHERE h.Date > fc.CompleteDate -- 仅保留完成后的状态记录
)
-- 筛选出首次变更记录并关联完成日期
SELECT 
    pc.FormId,
    fc.CompleteDate,
    pc.Status,
    pc.FirstUpdateDate
FROM PostCompleteChanges pc
JOIN FormComplete fc ON pc.FormId = fc.FormId
WHERE pc.rn = 1 -- 只取完成后的第一条变更记录
ORDER BY pc.FormId;

逻辑解释 #

1. FormComplete CTE：按FormId分组，统计每个表单第一次进入Completed状态的日期，确保拿到的是最早的完成时间。
2. PostCompleteChanges CTE：关联完成日期，筛选出所有完成后的状态记录，用ROW_NUMBER()按日期升序给每个表单的完成后记录编号，编号为1的就是首次变更记录。
3. 最后筛选出编号为1的记录，关联完成日期，得到最终结果。

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