含子查询的SQL语句中使用count()函数报错求助

报错原因 #

你遇到的错误Cannot perform an aggregate function on an expression containing an aggregate or a subquery，是因为SQL不允许在聚合函数（比如COUNT）中直接嵌套包含其他聚合函数（比如MAX）或子查询的表达式。你的修改尝试直接把带MAX的子查询放进COUNT里，违反了这个规则。同时注意原SQL中A.STATUS ('NEW')存在语法错误，缺少等号，应为A.STATUS = 'NEW'。

修正方案 #

下面提供两种可行的解决方法：

方法一：使用CTE先获取原始结果再统计

先通过CTE（公共表表达式）生成原查询的结果集，再在外层对结果集中的字段进行计数：

WITH OriginalResults AS (
    SELECT
        (SELECT MAX(LOG_DATE) FROM LOGTABLE WHERE ID=A.ID AND STATUS = 'NEW') AS DATE1,
        (SELECT MAX(LOG_DATE) FROM LOGTABLE WHERE ID=A.ID AND STATUS= 'OLD') AS DATE
    FROM TABLE1 A
    WHERE 
        A.STATUS = 'NEW' OR 
        A.ID IN (
            SELECT DISTINCT ID 
            FROM LOGTABLE 
            WHERE LOG_DATE BETWEEN @DATETIME AND @DATETIME AND STATUS = 'OLD'
        )
)
SELECT
    COUNT(DATE1) AS DATE1_COUNT,
    COUNT(DATE) AS DATE_COUNT
FROM OriginalResults;

方法二：用LEFT JOIN预聚合日志表（更高效）

通过提前对LOGTABLE按ID和STATUS聚合，再与TABLE1关联，避免嵌套子查询，同时提升查询效率：

SELECT
    COUNT(NEW_LOG.MAX_DATE) AS DATE1_COUNT,
    COUNT(OLD_LOG.MAX_DATE) AS DATE_COUNT
FROM TABLE1 A
LEFT JOIN (
    SELECT ID, MAX(LOG_DATE) AS MAX_DATE
    FROM LOGTABLE
    WHERE STATUS = 'NEW'
    GROUP BY ID
) NEW_LOG ON A.ID = NEW_LOG.ID
LEFT JOIN (
    SELECT ID, MAX(LOG_DATE) AS MAX_DATE
    FROM LOGTABLE
    WHERE STATUS = 'OLD'
    GROUP BY ID
) OLD_LOG ON A.ID = OLD_LOG.ID
WHERE 
    A.STATUS = 'NEW' OR
    A.ID IN (
        SELECT DISTINCT ID 
        FROM LOGTABLE 
        WHERE LOG_DATE BETWEEN @DATETIME AND @DATETIME AND STATUS = 'OLD'
    );

说明 #

两种方法都是先获取每个符合条件的TABLE1.ID对应的NEW/OLD状态最新日期，再统计这些日期不为NULL的记录数（COUNT会自动忽略NULL值，正好符合你统计有有效MAX(LOG_DATE)数量的需求）。

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