如何将表值按字段匹配写入CASE表达式实现状态映射

需求概述 #

需要将存储作业工序状态的行式数据（表B）转换为列式结果（表A），核心逻辑为：若表B中存在对应作业编号和工序的状态则使用该状态，否则显示'N/A'；同时完善现有查询中PPStat字段的CASE判断逻辑。

目标输出表（表A） #

源数据子查询（表B，对应代码中的ProcStepStats） #

完善后的SQL查询代码 #

SELECT 
    CASE 
        WHEN SOI.[ValueStream] = '2D' OR SOI.[ValueStream] = 'TS' THEN '2D/TS'
        ELSE '3D' 
    END AS [VS],
    -- 完善PPStat字段的CASE逻辑：匹配Pouch Pulling工序状态
    CASE 
        WHEN Holds.[HoldSteps] LIKE '%Pouch Pulling%' THEN 'Hold' 
        WHEN SOI.[Pouch Pulling Time] = 0 THEN 'N/A' 
        WHEN ProcStepStats_PP.[TranslatedStat] IS NOT NULL THEN 
            CASE ProcStepStats_PP.[TranslatedStat] 
                WHEN 'IP' THEN 'In Process' 
                ELSE ProcStepStats_PP.[TranslatedStat] 
            END
        ELSE 'Waiting' 
    END AS [PPStat],
    -- 输出表A要求的作业编号与工序状态列
    JobList.[Job Number],
    COALESCE(
        CASE ProcStepStats_A.[TranslatedStat] 
            WHEN 'IP' THEN 'In Process' 
            ELSE ProcStepStats_A.[TranslatedStat] 
        END, 
        'N/A'
    ) AS [Process Step A],
    COALESCE(
        CASE ProcStepStats_B.[TranslatedStat] 
            WHEN 'IP' THEN 'In Process' 
            ELSE ProcStepStats_B.[TranslatedStat] 
        END, 
        'N/A'
    ) AS [Process Step B],
    COALESCE(
        CASE ProcStepStats_C.[TranslatedStat] 
            WHEN 'IP' THEN 'In Process' 
            ELSE ProcStepStats_C.[TranslatedStat] 
        END, 
        'N/A'
    ) AS [Process Step C]
FROM 
(
    SELECT A.[Job Number], A.[Product], A.[Quantity], A.[Release Week] 
    FROM [Job Planning Info] AS A 
    WHERE A.[Released] = 'Yes'
    UNION ALL 
    SELECT B.[Remake Job Number], A.[Product], B.[Remake Quantity], A.[Release Week] 
    FROM [Remake Tracker] AS B
    INNER JOIN [Job Planning Info] AS A ON A.[Job Number] = B.[Original Job Number]  
    WHERE A.[Release Week] IS NOT NULL
) AS JobList
LEFT JOIN
(
    SELECT [Job Number], STRING_AGG(CAST([Process Step] AS NVARCHAR(MAX)), ',') AS HoldSteps 
    FROM [Hold Tracker] 
    GROUP BY [Job Number]
) AS Holds 
    ON JobList.[Job Number] = Holds.[Job Number]
LEFT JOIN [Second Ops Info] AS SOI 
    ON Joblist.[Product] = SOI.[Part Number]
-- 为每个目标工序单独左连接，获取对应最新状态
LEFT JOIN 
(
    SELECT JTL.[Job Number], JTL.[Process Step], 
        CASE 
            WHEN JTL.[Transaction Type] LIKE '%Start Job%' THEN 'IP' 
            WHEN JTL.[Transaction Type] LIKE '%Job Completed%' THEN 'Completed' 
            ELSE 'Waiting' 
        END AS [TranslatedStat] 
    FROM [Job Transaction List] JTL
    WHERE JTL.[Job Number] <> '' AND JTL.[TimeStamp] >= DATEADD(Month, -1, GETDATE())
    AND JTL.[TimeStamp] = (
        SELECT MAX(JTL2.[TimeStamp]) 
        FROM [Job Transaction List] JTL2 
        WHERE JTL.[Job Number] = JTL2.[Job Number] AND JTL.[Process Step] = JTL2.[Process Step]
    )
) AS ProcStepStats_A 
    ON JobList.[Job Number] = ProcStepStats_A.[Job Number] 
    AND ProcStepStats_A.[Process Step] = 'Process Step A'
LEFT JOIN 
(
    SELECT JTL.[Job Number], JTL.[Process Step], 
        CASE 
            WHEN JTL.[Transaction Type] LIKE '%Start Job%' THEN 'IP' 
            WHEN JTL.[Transaction Type] LIKE '%Job Completed%' THEN 'Completed' 
            ELSE 'Waiting' 
        END AS [TranslatedStat] 
    FROM [Job Transaction List] JTL
    WHERE JTL.[Job Number] <> '' AND JTL.[TimeStamp] >= DATEADD(Month, -1, GETDATE())
    AND JTL.[TimeStamp] = (
        SELECT MAX(JTL2.[TimeStamp]) 
        FROM [Job Transaction List] JTL2 
        WHERE JTL.[Job Number] = JTL2.[Job Number] AND JTL.[Process Step] = JTL2.[Process Step]
    )
) AS ProcStepStats_B 
    ON JobList.[Job Number] = ProcStepStats_B.[Job Number] 
    AND ProcStepStats_B.[Process Step] = 'Process Step B'
LEFT JOIN 
(
    SELECT JTL.[Job Number], JTL.[Process Step], 
        CASE 
            WHEN JTL.[Transaction Type] LIKE '%Start Job%' THEN 'IP' 
            WHEN JTL.[Transaction Type] LIKE '%Job Completed%' THEN 'Completed' 
            ELSE 'Waiting' 
        END AS [TranslatedStat] 
    FROM [Job Transaction List] JTL
    WHERE JTL.[Job Number] <> '' AND JTL.[TimeStamp] >= DATEADD(Month, -1, GETDATE())
    AND JTL.[TimeStamp] = (
        SELECT MAX(JTL2.[TimeStamp]) 
        FROM [Job Transaction List] JTL2 
        WHERE JTL.[Job Number] = JTL2.[Job Number] AND JTL.[Process Step] = JTL2.[Process Step]
    )
) AS ProcStepStats_C 
    ON JobList.[Job Number] = ProcStepStats_C.[Job Number] 
    AND ProcStepStats_C.[Process Step] = 'Process Step C'
-- 单独连接Pouch Pulling工序状态，用于PPStat字段判断
LEFT JOIN 
(
    SELECT JTL.[Job Number], JTL.[Process Step], 
        CASE 
            WHEN JTL.[Transaction Type] LIKE '%Start Job%' THEN 'IP' 
            WHEN JTL.[Transaction Type] LIKE '%Job Completed%' THEN 'Completed' 
            ELSE 'Waiting' 
        END AS [TranslatedStat] 
    FROM [Job Transaction List] JTL
    WHERE JTL.[Job Number] <> '' AND JTL.[TimeStamp] >= DATEADD(Month, -1, GETDATE())
    AND JTL.[TimeStamp] = (
        SELECT MAX(JTL2.[TimeStamp]) 
        FROM [Job Transaction List] JTL2 
        WHERE JTL.[Job Number] = JTL2.[Job Number] AND JTL.[Process Step] = JTL2.[Process Step]
    )
) AS ProcStepStats_PP 
    ON JobList.[Job Number] = ProcStepStats_PP.[Job Number] 
    AND ProcStepStats_PP.[Process Step] = 'Pouch Pulling'
LEFT JOIN [Live Schedule Automatic Pull] AS LSAP 
    ON JobList.[Job Number] = LSAP.[Job Number] 

关键逻辑说明 #

1. 工序状态列生成：针对每个目标工序（Process Step A/B/C）单独左连接状态子查询并筛选对应工序，用COALESCE处理无匹配状态时显示'N/A'，同时将子查询中的'IP'转换为目标表要求的'In Process'。
2. PPStat字段完善：新增Pouch Pulling工序的单独连接，当存在该工序状态时转换状态文本后返回，否则执行后续'Waiting'逻辑。
3. 性能优化方案：若[Job Transaction List]数据量较大，可将获取最新工序状态的逻辑提取为CTE（公共表表达式），避免重复执行相同查询：

WITH ProcStepStats AS (
    SELECT JTL.[Job Number], JTL.[Process Step], 
        CASE 
            WHEN JTL.[Transaction Type] LIKE '%Start Job%' THEN 'IP' 
            WHEN JTL.[Transaction Type] LIKE '%Job Completed%' THEN 'Completed' 
            ELSE 'Waiting' 
        END AS [TranslatedStat] 
    FROM [Job Transaction List] JTL
    WHERE JTL.[Job Number] <> '' AND JTL.[TimeStamp] >= DATEADD(Month, -1, GETDATE())
    AND JTL.[TimeStamp] = (
        SELECT MAX(JTL2.[TimeStamp]) 
        FROM [Job Transaction List] JTL2 
        WHERE JTL.[Job Number] = JTL2.[Job Number] AND JTL.[Process Step] = JTL2.[Process Step]
    )
)
-- 后续查询直接引用ProcStepStats，多次左连接时仅需筛选不同工序

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