Sum(Case When)函数使用异常求助：两次查询结果不一致

核心问题：CASE WHEN条件的逻辑分组错误 #

SQL中AND的优先级高于OR，你原查询的WHEN条件未正确用括号包裹OR连接的两个时间区间，导致逻辑判断偏离预期。例如第一个WHEN的条件：

((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') or (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00') and (Installation_ID = '18374' or Installation_ID = '18466' or Installation_ID = '18375' or Installation_ID = '18372' ))

实际会被数据库解析为：

(时间区间1) OR ((时间区间2) AND (安装ID集合))

由于你的查询通过WHERE Installation_Id = 18467过滤了目标安装ID，所有记录的安装ID都不在第一个WHEN的集合里，因此(时间区间2 AND 安装ID集合)永远为false。此时第一个WHEN的条件等价于仅满足时间区间1，导致所有18467且在7-9点的记录错误匹配到第一个WHEN分支，用availability/240计算，而非第三个WHEN分支的availability/48，最终总和被拉低，得到错误结果。

额外优化点：冗余的安装ID判断 #

你的查询已通过WHERE子句过滤了安装ID为18467的记录，第三个WHEN里的Installation_ID = '18467'属于冗余判断，可直接移除（若后续需查询多个安装ID，保留但需确保逻辑分组正确）。

修正后的查询 #

将每个WHEN中的时间区间条件用括号整体包裹，再与安装ID条件做AND判断，确保逻辑符合预期：

SELECT 
    CONVERT(VARCHAR(10), [datetime], 111) as [Date],
    Installation_Id,
    Installation_Name,
    ROUND(ISNULL(SUM(
        CASE 
            WHEN ((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') 
                  OR (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00')) 
                AND (Installation_ID IN ('18374','18466','18375','18372')) 
            THEN availability/240 
            WHEN ((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') 
                  OR (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00')) 
                AND (Installation_ID = '18373') 
            THEN availability/216 
            WHEN ((cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') 
                  OR (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00')) 
                AND (Installation_ID = '18467') 
            THEN availability/48 
        END
    ), 0), 4) as [Availability from 7am to 9am or 4pm to 8pm]
FROM Reserve_Power
WHERE Installation_Id = 18467
GROUP BY CONVERT(VARCHAR(10), [datetime], 111), Installation_Id, Installation_Name

若仅查询18467，可进一步简化CASE分支（前两个分支永远不会匹配）：

SELECT 
    CONVERT(VARCHAR(10), [datetime], 111) as [Date],
    Installation_Id,
    Installation_Name,
    ROUND(ISNULL(SUM(
        CASE 
            WHEN (cast([datetime] as time) >= '07:00:00' and cast([datetime] as time) < '09:00:00') 
                 OR (cast([datetime] as time) >= '16:00:00' and cast([datetime] as time) < '20:00:00')
            THEN availability/48 
        END
    ), 0), 4) as [Availability from 7am to 9am or 4pm to 8pm]
FROM Reserve_Power
WHERE Installation_Id = 18467
GROUP BY CONVERT(VARCHAR(10), [datetime], 111), Installation_Id, Installation_Name

验证说明 #

修正后的查询会正确将18467的时间区间记录匹配到availability/48分支，每个日期的计算结果会与你第二个单日期查询的结果一致，最终分组结果符合预期。

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