SQL Server中如何用CASE语句结合两表生成Status字段？

问题描述 #

我自学SQL有一段时间了，最近写查询时碰到个麻烦：需要在CASE语句里同时用两个不同数据表的条件来返回对应值。具体逻辑是：

• 如果Techman_WOCLOCK表（别名TC）里的ClockingStatus等于1，就返回'24'
• 否则，根据PROGRESS表（别名OP）里的Status字段：
  • 当值为'C'时返回99
  • 当值为'I'时返回50
  • 其他情况返回0

我目前的SQL语句如下：

SELECT TOP (100) PERCENT 
    RTRIM(OP.Standard_Op) AS TaskDescription,
    OP.Op_Number AS OpStep,
    CAST(CAST(OP.Works_Order_Number AS DECIMAL(12, 2)) AS NVARCHAR(50)) AS OrderCode,
    CASE OP.Status WHEN 'C' THEN 99 WHEN 'I' THEN 50 ELSE 0 END AS Status,
    CAST(OP.Op_Number AS varchar(50)) AS OpCode,
    CAST(CAST(OP.Works_Order_Number AS decimal(12, 2)) AS nvarchar(50)) + '_' + CAST(OP.Op_Number AS nvarchar(50)) AS JobCode,
    TC.ClockingStatus
FROM dbo.PROGRESS AS OP
INNER JOIN dbo.vOrcData_ActiveOperations AS AW 
    ON AW.Works_Order = OP.Works_Order_Number AND AW.Op_Number = OP.Op_Number
INNER JOIN dbo.WOHEAD AS WH 
    ON WH.Works_Order = OP.Works_Order_Number
INNER JOIN dbo.RESOURCE AS RR 
    ON RR.Resource_Code = OP.Original_Resource_Code
LEFT OUTER JOIN dbo.Techman_WOCLOCK AS TC 
    ON OP.Works_Order_Number = TC.OrderCode AND OP.Op_Number = TC.OpStep
LEFT OUTER JOIN dbo.MEMO_FIELDS_SQL 
    ON dbo.MEMO_FIELDS_SQL.Memo_Unique = OP.Man_Memo_Unique
LEFT OUTER JOIN (
    SELECT Allocated_WO_Num, Sub_Con_Op_No, MAX(Date_Promised) AS Date_Promised 
    FROM dbo.PODETAIL AS PODETAIL_1 
    GROUP BY Allocated_WO_Num, Sub_Con_Op_No
) AS PODETAIL 
    ON PODETAIL.Allocated_WO_Num = OP.Works_Order_Number AND PODETAIL.Sub_Con_Op_No = OP.Op_Number
LEFT OUTER JOIN (
    SELECT Works_Order, Operation, SUM(Operation_Time) AS TimeComplete 
    FROM dbo.WOCLOCK 
    GROUP BY Works_Order, Operation
) AS CLOCKEDTIME 
    ON WH.Works_Order = CLOCKEDTIME.Works_Order AND OP.Op_Number = CLOCKEDTIME.Operation

解决方案 #

你需要调整原来的CASE语句结构，把TC.ClockingStatus的判断放在最外层——因为这是优先级最高的条件。另外要注意，由于用的是LEFT OUTER JOIN，TC.ClockingStatus可能会出现NULL值，不过这种情况会自动进入ELSE分支，刚好符合你的逻辑。

这里提供两种写法，你可以选顺手的用：

写法1：嵌套CASE

CASE 
    WHEN TC.ClockingStatus = 1 THEN '24'
    ELSE 
        CASE OP.Status 
            WHEN 'C' THEN '99'
            WHEN 'I' THEN '50'
            ELSE '0'
        END
END AS Status

写法2：简洁的搜索式CASE

CASE 
    WHEN TC.ClockingStatus = 1 THEN '24'
    WHEN OP.Status = 'C' THEN '99'
    WHEN OP.Status = 'I' THEN '50'
    ELSE '0'
END AS Status

把上面的代码替换掉原SQL里CASE OP.Status ... AS Status那一行就行。另外要注意返回值的类型一致性：如果'24'是数字，就去掉引号写成24，同时把99/50/0也保持数字类型，避免后续处理出现类型转换错误。

完整修改后的SQL如下：

SELECT TOP (100) PERCENT 
    RTRIM(OP.Standard_Op) AS TaskDescription,
    OP.Op_Number AS OpStep,
    CAST(CAST(OP.Works_Order_Number AS DECIMAL(12, 2)) AS NVARCHAR(50)) AS OrderCode,
    -- 修改后的CASE语句
    CASE 
        WHEN TC.ClockingStatus = 1 THEN '24'
        WHEN OP.Status = 'C' THEN '99'
        WHEN OP.Status = 'I' THEN '50'
        ELSE '0'
    END AS Status,
    CAST(OP.Op_Number AS varchar(50)) AS OpCode,
    CAST(CAST(OP.Works_Order_Number AS decimal(12, 2)) AS nvarchar(50)) + '_' + CAST(OP.Op_Number AS nvarchar(50)) AS JobCode,
    TC.ClockingStatus
FROM dbo.PROGRESS AS OP
INNER JOIN dbo.vOrcData_ActiveOperations AS AW 
    ON AW.Works_Order = OP.Works_Order_Number AND AW.Op_Number = OP.Op_Number
INNER JOIN dbo.WOHEAD AS WH 
    ON WH.Works_Order = OP.Works_Order_Number
INNER JOIN dbo.RESOURCE AS RR 
    ON RR.Resource_Code = OP.Original_Resource_Code
LEFT OUTER JOIN dbo.Techman_WOCLOCK AS TC 
    ON OP.Works_Order_Number = TC.OrderCode AND OP.Op_Number = TC.OpStep
LEFT OUTER JOIN dbo.MEMO_FIELDS_SQL 
    ON dbo.MEMO_FIELDS_SQL.Memo_Unique = OP.Man_Memo_Unique
LEFT OUTER JOIN (
    SELECT Allocated_WO_Num, Sub_Con_Op_No, MAX(Date_Promised) AS Date_Promised 
    FROM dbo.PODETAIL AS PODETAIL_1 
    GROUP BY Allocated_WO_Num, Sub_Con_Op_No
) AS PODETAIL 
    ON PODETAIL.Allocated_WO_Num = OP.Works_Order_Number AND PODETAIL.Sub_Con_Op_No = OP.Op_Number
LEFT OUTER JOIN (
    SELECT Works_Order, Operation, SUM(Operation_Time) AS TimeComplete 
    FROM dbo.WOCLOCK 
    GROUP BY Works_Order, Operation
) AS CLOCKEDTIME 
    ON WH.Works_Order = CLOCKEDTIME.Works_Order AND OP.Op_Number = CLOCKEDTIME.Operation

小提醒 #

如果你的业务里存在TC.ClockingStatus为NULL但希望特殊处理的情况，可以在CASE里加个WHEN TC.ClockingStatus IS NULL THEN ...的分支，不过从你的需求描述看，目前的写法已经足够覆盖场景了。

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