SQL Server 2017中能否在CASE表达式内使用子查询？

SQL Server允许在CASE语句中使用子查询，但不允许在被聚合函数（如SUM）直接包裹的CASE表达式里包含子查询，这就是你遇到报错的原因。

针对你的需求，提供两种可行的改写方案：

方案1：用CTE预生成列表后左连接统计 #

这种方式将子查询提前执行，生成OEM号码列表，再通过左连接判断呼叫号码是否属于OEM范围，最后统计非OEM的数量：

WITH oem_numbers AS (
    SELECT CONCAT('1', phone_number) AS oem_phone
    FROM directory
    WHERE description = 'OEM'
)
SELECT 
    SUM(CASE WHEN oem_numbers.oem_phone IS NULL THEN 1 ELSE 0 END) AS non_oem_out
FROM 
    call_legs
LEFT JOIN oem_numbers ON call_legs.to_phone_number = oem_numbers.oem_phone
WHERE 
    call_legs.leg_order = 0
    AND call_legs.type = 'Voice'
    AND call_legs.result != 'Blocked'
    AND call_legs.start_time >= '2022-10-09 00:00:00'
    AND call_legs.start_time <= '2022-10-16 23:59:00';

方案2：用NOT EXISTS替代NOT IN改写 #

NOT EXISTS可以避免NOT IN遇到NULL值时的逻辑陷阱，同时调整聚合方式适配SQL Server的语法：

SELECT 
    COUNT(CASE WHEN NOT EXISTS (
        SELECT 1 
        FROM directory 
        WHERE description = 'OEM' 
          AND CONCAT('1', phone_number) = call_legs.to_phone_number
    ) THEN 1 END) AS non_oem_out
FROM 
    call_legs
WHERE 
    leg_order = 0
    AND type = 'Voice'
    AND result != 'Blocked'
    AND start_time >= '2022-10-09 00:00:00'
    AND start_time <= '2022-10-16 23:59:00';

补充说明 #

• 方案1的性能通常更优，尤其当directory表数据量较大时，预生成列表后连接的效率更高；
• 避免使用NOT IN：如果directory表的phone_number字段存在NULL值，NOT IN会返回空结果，而NOT EXISTS不会有这个问题。

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