多表SQL查询中Area_Numbers列NULL显示异常及聚合报错问题

我的SQL查询需从5个关联表中获取数据，当前问题是Area_Numbers列无值时显示括号而非NULL，生成该列的原SQL语句如下：

CONCAT('(',(STRING_AGG((CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 AND UCD.CustomFieldID LIKE '1' THEN SUBSTRING(AccessLevels.Name,LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, (LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name)))-LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name)))-1)) ELSE NULL END), ')(')), ')') AS Area_Numbers

尝试1：添加外层CASE条件过滤无值行 #

修改后的语句：

CASE WHEN UCD.CustomFieldID LIKE '1' THEN (CONCAT('(',(STRING_AGG((CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 AND UCD.CustomFieldID LIKE '1' THEN SUBSTRING(AccessLevels.Name,LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, (LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name)))-LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name)))-1)) ELSE NULL END), ')(')), ')')) ELSE NULL END AS Area_Numbers

报错信息：

Msg 8120, Level 16, State 1, Line 4
Column 'UserCustomFieldGroupData.CustomFieldID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

尝试2：添加MAX聚合函数解决GROUP BY报错 #

修改后的语句：

max(CASE WHEN UCD.CustomFieldID LIKE '1' THEN (CONCAT('(',(STRING_AGG((CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 AND UCD.CustomFieldID LIKE '1' THEN SUBSTRING(AccessLevels.Name,LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, (LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name)))-LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name)))-1)) ELSE NULL END), ')(')), ')')) ELSE NULL END) AS Area_Numbers

报错信息：

Msg 130, Level 15, State 1, Line 5
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

尝试3：将CONCAT移至CASE的THEN分支内 #

修改后的语句：

CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 AND UCD.CustomFieldID LIKE '1' THEN CONCAT('(',(STRING_AGG((SUBSTRING(AccessLevels.Name,LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, (LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name)))-LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name)))-1))), ')(')), ')') ELSE NULL END AS Area_Numbers

报错信息：

Msg 8120, Level 16, State 1, Line 6
Column 'AccessLevels.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 6
Column 'AccessLevels.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 6
Column 'UserCustomFieldGroupData.CustomFieldID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

尝试4：在尝试3的基础上添加MAX聚合函数 #

修改后的语句：

max(CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 AND UCD.CustomFieldID LIKE '1' THEN CONCAT('(',(STRING_AGG((SUBSTRING(AccessLevels.Name,LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, (LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name)))-LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name)))-1))), ')(')), ')') ELSE NULL END) AS Area_Numbers

报错信息：

Msg 130, Level 15, State 1, Line 7
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

解决方案 #

问题根源有两个：

1. 原语句中，当STRING_AGG没有可聚合的非NULL值时返回NULL，外层CONCAT('(', NULL, ')')会生成()而非NULL；
2. SQL不允许嵌套聚合函数（如MAX包裹STRING_AGG），且非聚合列必须出现在GROUP BY子句中。

方案1：用双层CASE判断聚合结果

先计算STRING_AGG的结果，若为空则返回NULL，否则拼接括号；同时确保UCD.CustomFieldID符合聚合规则：

CASE 
    WHEN MAX(UCD.CustomFieldID) LIKE '1' THEN
        CASE 
            WHEN STRING_AGG(
                CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 THEN 
                    SUBSTRING(AccessLevels.Name, 
                        LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, 
                        LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name))) - LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) - 1
                    ) 
                ELSE NULL 
                END, 
                ')('
            ) IS NOT NULL THEN
                CONCAT('(', STRING_AGG(
                    CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 THEN 
                        SUBSTRING(AccessLevels.Name, 
                            LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, 
                            LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name))) - LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) - 1
                        ) 
                    ELSE NULL 
                    END, 
                    ')('
                ), ')')
            ELSE NULL
        END
    ELSE NULL
END AS Area_Numbers

方案2：用NULLIF简化判断

直接将CONCAT生成的空括号()替换为NULL，写法更简洁：

CASE WHEN MAX(UCD.CustomFieldID) LIKE '1' THEN
    NULLIF(
        CONCAT('(', STRING_AGG(
            CASE WHEN LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) = 20 THEN 
                SUBSTRING(AccessLevels.Name, 
                    LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) + 1, 
                    LEN(LEFT(AccessLevels.Name, CHARINDEX ('>', AccessLevels.Name))) - LEN(LEFT(AccessLevels.Name, CHARINDEX ('<', AccessLevels.Name))) - 1
                ) 
            ELSE NULL 
            END, 
            ')('
        ), ')'),
        '()'
    )
ELSE NULL
END AS Area_Numbers

注意：如果UCD.CustomFieldID是分组键，可以直接用UCD.CustomFieldID LIKE '1'替代MAX(UCD.CustomFieldID) LIKE '1'，同时确保GROUP BY子句包含所有非聚合列。

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