如何在T-SQL查询结果中移除CASE语句生成的输出列

解决方案 #

你可以通过以下两种T-SQL层面的方式实现返回结果不含CASE相关列：

1. 直接移除SELECT列表中的CASE列（最简便）

你当前的WHERE条件已经明确写了t.TimeCallWasReceived IS NULL，所以你写在SELECT里的CASE逻辑实际没有业务作用，满足WHERE条件的行执行CASE时都会进入WHEN t.TimeCallWasReceived IS NULL的分支，返回的是NULL值，完全不需要作为输出列存在。直接删除SELECT列表中整段CASE语句即可，查询的过滤逻辑、兼容双场景的能力都不会受影响，修改后的查询如下：

SELECT DISTINCT
       c.Num,
       'Notified for pickup ' + (CONVERT(varchar(30), c.ServiceCalled, 100)) + '. Number ' + c.Num AS Issue,
       RTRIM(c.NameFirst) + ' ' + RTRIM(c.NameLast) AS CusName
FROM tblCustomer c
     LEFT JOIN tblTransportation t ON t.Num = c.Num
WHERE t.TimeCallWasReceived IS NULL
  AND c.Type = 'Type1'
  AND c.ServiceCalled IS NOT NULL
  AND c.TransportNum IS NULL
  AND LEFT(c.Num, 2) <> 'CR'
  AND LEFT(c.Num, 2) <> 'TR'
  AND c.Status NOT IN ('Void', 'Closed') -- 原OR逻辑永远为真，修正为正确写法
  AND ((DATEDIFF(MINUTE, c.ServiceCalled, GETDATE())) > 180)
  AND c.Type <> 'VOID'
  AND c.Type <> 'NON'
  AND c.Type <> 'Walk-In'
  AND c.Type <> 'CFR'
  AND c.CaseType <> 'PAP'
  AND c.ServiceCalled >= DATEADD(DAY, -2, GETDATE())
ORDER BY c.Num DESC;

2. 子查询/CTE封装（适用于需要保留CASE逻辑做中间判断的场景）

如果你后续需要调整查询兼容t.TimeCallWasReceived非空的场景，必须保留CASE逻辑做中间计算/过滤，可以用CTE把包含CASE的查询包起来，外层只选择你需要返回的列即可，示例如下：

WITH temp_result AS (
    SELECT DISTINCT
           c.Num,
           'Notified for pickup ' + (CONVERT(varchar(30), c.ServiceCalled, 100)) + '. Number ' + c.Num AS Issue,
           RTRIM(c.NameFirst) + ' ' + RTRIM(c.NameLast) AS CusName,
           (CASE
                 WHEN t.TimeCallWasReceived IS NULL THEN CONVERT(varchar(20), t.TimeCallWasReceived, 113)
                 ELSE 'NO'
            END) AS CallReceivedAt
    FROM tblCustomer c
         LEFT JOIN tblTransportation t ON t.Num = c.Num
    WHERE t.TimeCallWasReceived IS NULL
      AND c.Type = 'Type1'
      AND c.ServiceCalled IS NOT NULL
      AND c.TransportNum IS NULL
      AND LEFT(c.Num, 2) <> 'CR'
      AND LEFT(c.Num, 2) <> 'TR'
      AND c.Status NOT IN ('Void', 'Closed')
      AND ((DATEDIFF(MINUTE, c.ServiceCalled, GETDATE())) > 180)
      AND c.Type <> 'VOID'
      AND c.Type <> 'NON'
      AND c.Type <> 'Walk-In'
      AND c.Type <> 'CFR'
      AND c.CaseType <> 'PAP'
      AND c.ServiceCalled >= DATEADD(DAY, -2, GETDATE())
)
SELECT Num, Issue, CusName 
FROM temp_result
ORDER BY Num DESC;

额外优化提示 #

你原查询中的(c.Status <> 'Void' OR c.Status <> 'Closed')逻辑存在问题：任意状态都不可能同时等于Void和Closed，所以这个条件永远为真，相当于没有加过滤，建议替换为c.Status NOT IN ('Void', 'Closed')实现你预期的过滤效果。

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