SQL使用CTE传递CASE结果分组聚合报8120错误咨询

SQL Server的分组聚合有强制校验规则：SELECT子句中出现的所有非聚合计算字段，必须全部包含在GROUP BY分组字段列表中。
你的CTE_2中写了SELECT *，会把CTE_1里的分钟级字段TimeDate一并查询出来，但CTE_2的GROUP BY仅按年、月、日三个维度分组，单个自然日对应数百条不同的分钟级TimeDate值，SQL引擎无法确定要返回哪一个时间值，因此触发8120错误。

核心修改逻辑：

• 移除CTE_2中SELECT *的写法，仅显式声明需要返回的分组维度字段、聚合计算字段，不需要把分钟级的TimeDate带入日维度聚合结果
• （可选优化）将年、月、日的时间转换逻辑下沉到CTE_1，避免重复编写相同的时间计算函数，代码可读性更高
• 额外补全除零判断，避免Sum_Expected_KWh为0时触发除零错误；字段别名统一使用方括号包裹，符合SQL Server规范，避免关键字冲突
• 修正整数除法精度丢失问题，原代码中整数除以整数会直接截断小数位，无法得到正确的百分比结果

修正后的完整代码如下：

WITH
    CTE_1 AS
        (SELECT        
                POIData.Timestamp_UTC as [TimeDate],
                -- 提前计算年月日维度，避免CTE_2重复编写计算逻辑
                DATEPART(YY,DATEADD(HOUR,-6,POIData.Timestamp_UTC)) as [Year],
                DATEPART(MM,DATEADD(HOUR,-6,POIData.Timestamp_UTC)) as [Mon],
                DATEPART(DD,DATEADD(HOUR,-6,POIData.Timestamp_UTC)) as [Day],
                SUM(POIData.Meter_KW/60) as [Sum_Meter_KWh],
                SUM(POIData.Park_Potential_KW/60) as [Sum_Expected_KWh],
                SUM(CASE
                        WHEN (POIData.Park_Potential_KW > POIData.Meter_KW)
                            AND POIData.Meter_KW>=0
                            AND POIData.Park_Potential_KW>=0      
                            AND (WeatherStationData.Valid = 1)
                            AND (POIData.Valid = 1)
                            AND (WeatherStationData.GPOA_1 > 50)
                            AND (POIData.Power_Limit_SP < 120000)
                            AND (POIData.Park_Potential_KW > POIData.Power_Limit_SP)
                        THEN (POIData.Park_Potential_KW/60) - (POIData.Meter_KW/60)
                        WHEN (POIData.Park_Potential_KW <= POIData.Meter_KW) OR POIData.Park_Potential_KW<0
                        THEN 0
                        END 
                ) AS [Curtailed_Energy_KWh],
                
                COUNT(CASE
                        WHEN InverterData.Valid = 1
                            AND InverterData.Active_Power > 0
                            AND  WeatherStationData.GPOA_1 > 100
                        THEN InverterData.fkInverterID
                        END
                ) AS [Inv_Count]
        FROM        [Comanche].[dbo].POIData INNER JOIN
                    [Comanche].[dbo].InverterData ON POIData.Timestamp_UTC = InverterData.Timestamp_UTC INNER JOIN
                    [Comanche].[dbo].WeatherStationData ON POIData.Timestamp_UTC = WeatherStationData.Timestamp_UTC
        WHERE       DATEADD(HOUR,-6,POIData.Timestamp_UTC) >= '2022-05-01' and DATEADD(HOUR,-6,POIData.Timestamp_UTC) < '2022-06-01'
        GROUP BY    POIData.Timestamp_UTC
    ),
    
    CTE_2 AS
    (
        SELECT  
                [Year],
                [Mon],
                [Day],
                AVG(CASE
                        WHEN [Inv_Count]<300
                        THEN [Inv_Count]/300.0 -- 除以浮点型数值避免整数除法丢失小数精度
                        ELSE 1
                    END) AS [Availability_InvTime(%)],
                -- 增加除零判断，避免预期发电量为0时触发运算错误
                CASE WHEN SUM([Sum_Expected_KWh]) = 0 THEN 0
                     ELSE (SUM([Sum_Meter_KWh]) + SUM([Curtailed_Energy_KWh]))/SUM([Sum_Expected_KWh])
                END as [Energetic_Avail(%)]
        FROM CTE_1
        GROUP BY    
                    [Year],
                    [Mon],
                    [Day]
    )

SELECT * FROM CTE_2
ORDER BY [Year], [Mon], [Day]

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