SQL Server使用SUM聚合与ORDER BY时出现Msg 8120错误

运行以下SQL查询时触发错误：

SELECT
(
    CASE
        WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$."bbred"') ) ) ) >= 0 )
               AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$.&quot;bbred&quot;') ) ) ) <= 1000 ) ) THEN
            '58'
        WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$.&quot;bbred&quot;') ) ) ) >= 1001 )
               AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$.&quot;bbred&quot;') ) ) ) <= 10000 ) ) THEN
            '59'
        ELSE
            'Other'
    END
) AS cc_1658532399112,
SUM(CAST(((JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$.&quot;bbred&quot;'))) AS FLOAT)) AS "group_by_1658532512558_0"
FROM
    acc.pod.mnnorway_tup_vw multi_pod
ORDER BY
    (
        CASE
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$.&quot;bbred&quot;') ) ) ) >= 0 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$.&quot;bbred&quot;') ) ) ) <= 1000 ) ) THEN
                0
            WHEN ( ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$.&quot;bbred&quot;') ) ) ) >= 1001 )
                   AND ( convert(float, ( ( JSON_VALUE(convert(varchar(max), multi_pod.struc_ppod), '$.&quot;bbred&quot;') ) ) ) <= 10000 ) ) THEN
                1
            ELSE
                2
        END
    ) ASC;

报错信息：

Msg 8120, Level 16, State 1, Line 4
列'acc.pod.mnnorway_tup_vw.struc_ppod'在选择列表中无效，因为它未包含在聚合函数或GROUP BY子句中。

移除SUM语句后查询可正常执行，以下是解决方法：

问题原因 #

SQL Server的聚合查询规则要求：当SELECT语句包含聚合函数（如SUM）时，所有非聚合列/表达式必须出现在GROUP BY子句中。原查询中，SELECT里的CASE表达式属于非聚合逻辑，但未加入GROUP BY，导致数据库无法确定分组依据，触发报错。

修正后的查询 #

我们可以将SELECT中的CASE表达式加入GROUP BY，同时用CTE提取重复计算的bbred值，提升查询效率：

WITH pod_data AS (
    SELECT
        CAST(JSON_VALUE(convert(varchar(max), struc_ppod), '$."bbred"') AS FLOAT) AS bbred
    FROM acc.pod.mnnorway_tup_vw
)
SELECT
    CASE
        WHEN bbred >= 0 AND bbred <= 1000 THEN '58'
        WHEN bbred >= 1001 AND bbred <= 10000 THEN '59'
        ELSE 'Other'
    END AS cc_1658532399112,
    SUM(bbred) AS "group_by_1658532512558_0"
FROM pod_data
GROUP BY
    CASE
        WHEN bbred >= 0 AND bbred <= 1000 THEN '58'
        WHEN bbred >= 1001 AND bbred <= 10000 THEN '59'
        ELSE 'Other'
    END
ORDER BY
    CASE cc_1658532399112
        WHEN '58' THEN 0
        WHEN '59' THEN 1
        ELSE 2
    END ASC;

说明 #

1. 用CTE提前提取bbred值，避免重复调用JSON_VALUE和类型转换函数，减少计算开销
2. 将SELECT中的CASE表达式加入GROUP BY，满足SQL Server的聚合查询规则
3. 简化ORDER BY逻辑，直接通过别名判断排序优先级，代码更易读

内容的提问来源于stack exchange，提问作者prashant.kr.mod