如何在SQL CASE语句的AVG中使用ANY运算符及修正县平均异常

需求与背景 #

我们有一个用于计算本地雪况报告平均值的存储过程，输出的县百分比会用来给交互式地图着色。当前存在问题：部分县只有少量报告时，哪怕存在营业商家，平均值仍会被设为0，导致地图错误显示该县无营业商家。

核心需求：

• 若该县存在至少1份状态为Poor、Fair、Good、Excellent的报告，平均值设为25；
• 若此类报告多于1份，平均值设为50。

当前存储过程代码 #

ALTER PROCEDURE [dbo].[GetAverageConditionByCounty]
(   
    @SiteId int = 100
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cg.Name,
    AVG(ISNULL(CASE sn.Condition 
        WHEN 'Poor' THEN 25 
        WHEN 'Fair' THEN 50 
        WHEN 'Good' THEN 75 
        WHEN 'Excellent' THEN 100
        ELSE 0  END,-1)) as average
    FROM Counties cg    
    INNER JOIN BaseReports br ON br.CountyId = cg.Id AND br.IsActive = 1
    INNER JOIN SnowReport sn ON sn.BaseReportId = br.Id 
    WHERE br.SiteId = @SiteId
    GROUP BY cg.Id, cg.Name
    ORDER BY cg.Name
END

原查询示例结果 #

错误尝试与报错信息 #

使用ANY运算符的尝试 #

ALTER PROCEDURE [dbo].[GetAverageConditionByCounty]
(   
    @SiteId int = 100
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cg.Name,
    AVG(ISNULL(CASE sn.Condition 
        WHEN 'Past Peak' THEN 101
        WHEN 'Poor' THEN 25 
        WHEN 'Fair' THEN 50 
        WHEN 'Good' THEN 75 
        WHEN 'Excellent' THEN 100
        WHEN sn.Condition = ANY ('Poor', 'Fair', 'Good', 'Excellent' ) THEN 25
        ELSE 0  END,-1)) as average
    FROM Counties cg    
    INNER JOIN BaseReports br ON br.CountyId = cg.Id AND br.IsActive = 1
    INNER JOIN SnowReport sn ON sn.BaseReportId = br.Id 
    WHERE br.SiteId = @SiteId
    GROUP BY cg.Id, cg.Name
    ORDER BY cg.Name
END

报错：

• Incorrect syntax near '='.
• Incorrect syntax near 'Poor'. Expecting '(' or 'SELECT'.
• Incorrect syntax near 'THEN'.

使用IN运算符的尝试 #

ALTER PROCEDURE [dbo].[GetAverageConditionByCounty]
(   
    @SiteId int = 100
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT cg.Name,
    AVG(ISNULL(CASE sn.Condition 
        WHEN 'Past Peak' THEN 101
        WHEN 'Poor' THEN 25 
        WHEN 'Fair' THEN 50 
        WHEN 'Good' THEN 75 
        WHEN 'Excellent' THEN 100
        WHEN sn.Condition IN ('Poor', 'Fair', 'Good', 'Excellent' ) THEN 25
        ELSE 0  END,-1)) as average
    FROM Counties cg    
    INNER JOIN BaseReports br ON br.CountyId = cg.Id AND br.IsActive = 1
    INNER JOIN SnowReport sn ON sn.BaseReportId = br.Id 
    WHERE br.SiteId = @SiteId
    GROUP BY cg.Id, cg.Name
    ORDER BY cg.Name
END

报错：

• Incorrect syntax near 'IN'.
• Incorrect syntax near 'THEN'. Expecting ',', 'AND', or 'Or'.

解决方案 #

问题根源在于你使用了简单CASE表达式（CASE 列 WHEN 值 THEN ...），这种写法无法直接搭配IN或ANY条件，需改用搜索CASE表达式（CASE WHEN 条件 THEN ...）。同时需求是基于县内有效报告的数量赋值，所以要先统计数量再判断。

正确存储过程代码：

ALTER PROCEDURE [dbo].[GetAverageConditionByCounty]
(   
    @SiteId int = 100
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        cg.Name,
        -- 根据有效报告数设置平均值
        CASE 
            WHEN COUNT(CASE WHEN sn.Condition IN ('Poor', 'Fair', 'Good', 'Excellent') THEN 1 END) >= 1 THEN 
                CASE 
                    WHEN COUNT(CASE WHEN sn.Condition IN ('Poor', 'Fair', 'Good', 'Excellent') THEN 1 END) > 1 THEN 50
                    ELSE 25
                END
            ELSE 0 -- 无有效报告时设为0
        END AS average
    FROM Counties cg    
    INNER JOIN BaseReports br ON br.CountyId = cg.Id AND br.IsActive = 1
    INNER JOIN SnowReport sn ON sn.BaseReportId = br.Id 
    WHERE br.SiteId = @SiteId
    GROUP BY cg.Id, cg.Name
    ORDER BY cg.Name
END

代码说明 #

1. 用COUNT(CASE WHEN sn.Condition IN (...) THEN 1 END)统计每个县内符合条件的有效报告数量；
2. 外层CASE根据统计结果赋值：
   • 有效报告数≥1时，判断数量是否>1：是则设为50，否则设为25；
   • 无有效报告时保持0不变。

此代码可完全满足需求，解决少量报告导致平均值错误的问题。

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