SSMS中如何用CASE语句结合条件评估配对变量生成目标报表

核心思路 #

问题的关键是针对每个CaseID+State组合，先定位到最新的记录，再根据其Cleared值决定返回结果——这也是你之前用CASE+MAX聚合未得到预期结果的原因：直接聚合无法将最新的EventTimeStamp和对应的Cleared值正确绑定。

具体逻辑：

• 对每个CaseID和指定State的记录，按EventTimeStamp降序排序，标记出最新的那条记录
• 筛选出每个CaseID+State的最新记录后，判断Cleared值：
  • 若Cleared = 1，返回NULL
  • 若Cleared = 0，返回对应的EventTimeStamp
• 可按需将结果转成宽表（每个State作为单独列），适配报表展示需求

实现代码 #

第一步：获取每个CaseID+State的最新记录 #

WITH LatestCaseStates AS (
    SELECT 
        CaseID,
        State,
        EventTimeStamp,
        Cleared,
        -- 按CaseID和State分组，按时间降序标记序号，最新记录为1
        ROW_NUMBER() OVER (PARTITION BY CaseID, State ORDER BY EventTimeStamp DESC) AS RowNum
    FROM CaseTracking
    -- 筛选需要统计的State
    WHERE State IN ('OrderReceived', 'Completed')
)

第二步：生成长表格式的有效时间报表 #

SELECT 
    CaseID,
    State,
    CASE 
        WHEN Cleared = 1 THEN NULL
        ELSE EventTimeStamp
    END AS ValidTime
FROM LatestCaseStates
WHERE RowNum = 1;

第三步：生成宽表格式的有效时间报表（推荐用于查看） #

如果需要将不同State的结果展示为单独列，使用PIVOT转换：

WITH LatestCaseStates AS (
    SELECT 
        CaseID,
        State,
        EventTimeStamp,
        Cleared,
        ROW_NUMBER() OVER (PARTITION BY CaseID, State ORDER BY EventTimeStamp DESC) AS RowNum
    FROM CaseTracking
    WHERE State IN ('OrderReceived', 'Completed')
),
StateValidTimes AS (
    SELECT 
        CaseID,
        State,
        CASE WHEN Cleared = 1 THEN NULL ELSE EventTimeStamp END AS ValidTime
    FROM LatestCaseStates
    WHERE RowNum = 1
)
SELECT 
    CaseID,
    [OrderReceived] AS OrderReceived_ValidTime,
    [Completed] AS Completed_ValidTime
FROM StateValidTimes
PIVOT (
    MAX(ValidTime) FOR State IN ([OrderReceived], [Completed])
) AS PivotTable;

为什么之前的CASE+MAX写法会出错 #

举个典型场景：CaseID=1的Completed状态有两条记录：

• 记录1：EventTimeStamp='2024-01-02'，Cleared=0
• 记录2：EventTimeStamp='2024-01-03'，Cleared=1

如果用错误的CASE+MAX写法：

SELECT 
    CaseID,
    CASE WHEN MAX(CASE WHEN State='Completed' THEN Cleared END) = 1 THEN NULL ELSE MAX(CASE WHEN State='Completed' THEN EventTimeStamp END) END AS Completed_ValidTime
FROM CaseTracking
GROUP BY CaseID;

看似逻辑正确，但如果你的写法是将条件嵌套在CASE的AND中（比如CASE WHEN State='Completed' AND Cleared=0 THEN MAX(EventTimeStamp) END），就会因为分组后State不是单个值，导致无法关联最新时间和对应的Cleared状态，最终返回错误结果。

规则验证 #

针对你提到的序列场景：

• 序列0,1,0：最新记录的Cleared=0 → 返回最后一次0对应的时间
• 序列0,1,0,1：最新记录的Cleared=1 → 返回NULL
  上述代码会正确处理这两种情况，因为ROW_NUMBER()始终定位到最新的单条记录，再基于其Cleared值判断结果。

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