SQL子查询引用外部列报错：聚合表达式含外部引用时指定多列

错误原因 #

你遇到的Multiple columns are specified in an aggregated expression containing an outer reference错误，是因为标量子查询的聚合函数（SUM）中直接引用了外部表Table1的uf.SystemTypeId列，SQL引擎无法明确外部列与子查询聚合结果的关联逻辑，从而抛出错误。

解决方案 #

方案1：用WHERE条件替代CASE判断，改用COUNT统计 #

将匹配逻辑移到子查询的WHERE子句中，直接统计符合条件的行数，避免在聚合函数内引用外部列：

SELECT DISTINCT 
    uf.SystemName AS System,
    uf.SystemId,
    (
        SELECT COUNT(*)
        FROM OPENJSON((SELECT TOP 1 CAST(JsonStringColumn AS NVARCHAR(MAX)) FROM RefTable WHERE TagId = (SELECT TOP 1 TagId FROM CSSTAGS WHERE projectid='9abbeecf-15a4-412f-ba0c-358b8f09ac9e')))
        WITH (Data NVARCHAR(MAX) AS JSON) a
        CROSS APPLY OPENJSON(a.Data)
        WITH (System NVARCHAR(MAX) AS JSON) b
        CROSS APPLY OPENJSON(b.System) 
        WITH (SystemId NVARCHAR(MAX) '$.SystemId') c
        WHERE c.SystemId = uf.SystemTypeId
    ) AS SystemIdExists
FROM Table1 uf

方案2：使用CROSS APPLY关联子查询 #

将标量子查询转为CROSS APPLY关联查询，让SQL引擎明确外部列与子查询的逐行关联关系：

SELECT DISTINCT 
    uf.SystemName AS System,
    uf.SystemId,
    COALESCE(agg.SystemIdExists, 0) AS SystemIdExists
FROM Table1 uf
CROSS APPLY (
    SELECT SUM(CASE WHEN c.SystemId = uf.SystemTypeId THEN 1 ELSE 0 END) AS SystemIdExists
    FROM OPENJSON((SELECT TOP 1 CAST(JsonStringColumn AS NVARCHAR(MAX)) FROM RefTable WHERE TagId = (SELECT TOP 1 TagId FROM CSSTAGS WHERE projectid='9abbeecf-15a4-412f-ba0c-358b8f09ac9e')))
    WITH (Data NVARCHAR(MAX) AS JSON) a
    CROSS APPLY OPENJSON(a.Data)
    WITH (System NVARCHAR(MAX) AS JSON) b
    CROSS APPLY OPENJSON(b.System) 
    WITH (SystemId NVARCHAR(MAX) '$.SystemId') c
) agg

说明 #

两种方案都能解决错误：

• 方案1更简洁，适合仅需统计匹配行数的场景；
• 方案2保留了CASE逻辑的扩展性，若后续需要添加更多判断条件，直接修改CASE语句即可。

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