GROUP BY子句报错求助：列未包含在聚合函数或GROUP BY中

这个错误和表访问方式（JOIN）无关，核心问题是SQL语句的解析顺序导致别名无法在GROUP BY和WHERE中使用：

SQL的执行顺序是：FROM → JOIN → WHERE → GROUP BY → SELECT → ORDER BY。也就是说，WHERE和GROUP BY执行时，SELECT里定义的别名（比如DevDate）还没被创建，数据库根本找不到这个列。

修正方案1：直接替换别名对应的表达式 #

把WHERE和GROUP BY里的DevDate替换成CAST(a.allDates AS DATE)，直接使用原始转换逻辑：

SELECT TOP (100) 
    DevDate = CAST(a.allDates AS DATE), 
    FailsCount = COUNT(Test_Failure_Mode), 
    TotalCount = COUNT(*), 
    FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*) 
FROM 
    A_Inspections a 
JOIN 
    B_Inspections b ON a.test_ID = b.test_ID 
WHERE 
    CAST(a.allDates AS DATE) >= '2020-01-01' 
GROUP BY 
    CAST(a.allDates AS DATE)

修正方案2：用CTE提前处理日期（可读性更好） #

如果不想重复写CAST(a.allDates AS DATE)，可以用CTE先把转换后的日期计算好，后续直接用别名：

WITH InspectionsWithDate AS (
    SELECT 
        CAST(a.allDates AS DATE) AS DevDate,
        b.Test_Failure_Mode
    FROM A_Inspections a
    JOIN B_Inspections b ON a.test_ID = b.test_ID
    WHERE CAST(a.allDates AS DATE) >= '2020-01-01'
)
SELECT TOP (100)
    DevDate,
    FailsCount = COUNT(Test_Failure_Mode),
    TotalCount = COUNT(*),
    FailsRate = CAST(COUNT(Test_Failure_Mode) AS FLOAT)/ COUNT(*)
FROM InspectionsWithDate
GROUP BY DevDate

补充说明 #

• COUNT(Test_Failure_Mode)会自动忽略NULL值，所以它统计的是有失败模式的记录数，这个逻辑是对的。
• 如果Test_Failure_Mode本身不会为NULL，那FailsCount和TotalCount会相等，你可以根据实际业务确认这个字段的取值情况。

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