T-SQL中COUNT结合CASE语句报错的解决方法咨询

你碰到的这个错误Cannot perform an aggregate function on an expression containing an aggregate or a subquery，本质是因为你在COUNT聚合函数内部的CASE表达式里，又嵌套了另一个聚合函数MAX和子查询——SQL Server不允许这种嵌套聚合的写法，因为它会打乱聚合运算的执行逻辑。

先看看你原始代码里那个造成问题的子查询：

(SELECT CONVERT(DATE, MAX(Dates)) FROM (VALUES (S.SchedClosingDate), (S.SchedClosingDate)) AS SchedDates (Dates))

其实这里你把同一个日期S.SchedClosingDate重复放进VALUES然后取MAX，结果就是CONVERT(DATE, S.SchedClosingDate)本身，完全没必要用子查询和MAX，这就是导致嵌套聚合的根源。

修复方案1：简化条件表达式 #

直接把CASE里的判断简化成对S.SchedClosingDate的直接比较，这样就彻底避免了嵌套聚合的问题：

SELECT 
    COUNT(CASE WHEN CONVERT(DATE, S.SchedClosingDate) BETWEEN '2018-05-01' AND '2018-05-31' THEN FD.FileName END) AS [Scheduled to Close]
FROM FileData AS FD 
JOIN Status AS S ON FD.FileDataID = S.FileDataID

另外建议你使用YYYY-MM-DD这种标准日期格式，能避免因服务器日期格式设置不同导致的解析错误。

修复方案2：如果需要取多个日期字段的最大值 #

如果你原来的子查询是笔误，实际是想从多个不同的日期字段（比如计划关闭日期和实际关闭日期）中取最大值再判断范围，那可以用行级的CASE表达式来实现最大值逻辑，不用嵌套子查询：

SELECT 
    COUNT(CASE 
            WHEN CONVERT(DATE, 
                CASE WHEN S.SchedClosingDate > S.ActualClosingDate THEN S.SchedClosingDate ELSE S.ActualClosingDate END
            ) BETWEEN '2018-05-01' AND '2018-05-31' THEN FD.FileName END
        ) AS [Scheduled to Close]
FROM FileData AS FD 
JOIN Status AS S ON FD.FileDataID = S.FileDataID

要是你的SQL Server版本是2022及以上，还可以用内置的GREATEST函数简化最大值的获取：

SELECT 
    COUNT(CASE 
            WHEN CONVERT(DATE, GREATEST(S.SchedClosingDate, S.ActualClosingDate)) BETWEEN '2018-05-01' AND '2018-05-31' THEN FD.FileName END
        ) AS [Scheduled to Close]
FROM FileData AS FD 
JOIN Status AS S ON FD.FileDataID = S.FileDataID

为什么原来的写法不行？ #

SQL Server的聚合函数（比如COUNT）是对一组行做批量计算，而嵌套在它里面的子查询/聚合函数会试图对每一行单独做聚合计算，这两种逻辑冲突，所以被系统禁止。通过把嵌套的聚合逻辑转换成行级别的表达式（直接字段比较或行内CASE判断），既解决了报错问题，又能满足你在单个查询中统计多个指标的需求（不用把这个条件放到WHERE里影响其他指标的计算）。

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