SQL Server聚合函数含子查询报错：Oracle迁移查询修正咨询

错误信息 #

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

问题SQL Server代码 #

SELECT  epoch, sourceID,  
SUM(CASE (SELECT classification FROM CLASSIFICATION_lookup cl WHERE cl.Classification_Id = p.Proposal_ID)   
WHEN 'Class_A' THEN Estimate   ELSE 0  END) "Class_A" , 
SUM(CASE ISNULL((SELECT classification FROM CLASSIFICATION_lookup cl WHERE cl.Classification_Id = p.Proposal_ID), 'InvalidCls')   
WHEN 'NonValid' THEN Estimate ELSE 0  END) "NonValid" , 
SUM(CASE (SELECT classification FROM CLASSIFICATION_lookup cl WHERE cl.Classification_Id = p.Proposal_ID)   
WHEN 'Class_A'    THEN 0    
ELSE (CASE ISNULL((SELECT classification FROM CLASSIFICATION_lookup cl WHERE cl.Classification_Id = tr.TimeWorkTypeID), 'InvalidCls')           
WHEN 'NonValid'               THEN 0           
WHEN 'InvalidCls'               THEN Estimate           
ELSE Estimate       END)   END) "Authorized" ,      
SUM(CASE (SELECT classification FROM CLASSIFICATION_lookup cl WHERE cl.Classification_Id = tr.TimeWorkTypeID)    
WHEN 'General' THEN Estimate ELSE 0   END) "General" 
FROM  TimeReports TR 
LEFT OUTER JOIN  Proposals p ON tr.TimeWorkTypeID = p.ProposalCode 
WHERE  Accepted = 'Y'  
AND tr.epoch BETWEEN 100 AND 300 
GROUP BY  tr.epoch, tr.sourceID;

原Oracle参考代码 #

SELECT  epoch, sourceID,  
SUM(DECODE( (SELECT classification FROM CLASSIFICATION_lookup cl WHERE cl.Classification_Id = p.Proposal_ID), 'Class_A',   Estimate, 0) )          "Class_A" , 
SUM(DECODE ( NVL((SELECT classification FROM CLASSIFICATION_lookup cl WHERE cl.Classification_Id = p.Proposal_ID),'InvalidCls' ), 'NonValid', Estimate, 0) )          "NonValid" , 
SUM(DECODE ( (SELECT classification FROM CLASSIFICATION_lookup cl WHERE cl.Classification_Id = p.Proposal_ID), 'Class_A', 0,                
(DECODE( NVL((SELECT classification FROM CLASSIFICATION_lookup cl WHERE cl.Classification_Id = tr.TimeWorkTypeID),'InvalidCls'),                
'NonValid', 0,                 
'InvalidCls', Estimate, Estimate) ) )   )           "Authorized" ,      
SUM(DECODE ( (SELECT classification FROM CLASSIFICATION_lookup cl WHERE cl.Classification_Id = tr.TimeWorkTypeID),'General', Estimate, 0 ) )                "General" 
FROM  TimeReports TR, Proposals p 
WHERE Accepted = 'Y' AND tr.epoch between 100 AND 300 AND tr.TimeWorkTypeID = p.ProposalCode(+) 
GROUP BY tr.epoch, tr.sourceID;

修正方案 #

SQL Server不允许在聚合函数（如SUM）中嵌套子查询，这是和Oracle的核心语法差异。解决思路是将子查询转换为JOIN关联，提前获取需要的分类字段，避免在SUM内嵌套子查询，同时保证性能（JOIN比标量函数更高效，适合百万级数据场景）。

修正后的SQL代码 #

SELECT  
    tr.epoch, 
    tr.sourceID,  
    SUM(CASE WHEN cl_p.classification = 'Class_A' THEN tr.Estimate ELSE 0 END) AS "Class_A",
    SUM(CASE WHEN ISNULL(cl_p.classification, 'InvalidCls') = 'NonValid' THEN tr.Estimate ELSE 0 END) AS "NonValid",
    SUM(
        CASE 
            WHEN cl_p.classification = 'Class_A' THEN 0
            ELSE 
                CASE 
                    WHEN ISNULL(cl_tr.classification, 'InvalidCls') = 'NonValid' THEN 0
                    WHEN ISNULL(cl_tr.classification, 'InvalidCls') = 'InvalidCls' THEN tr.Estimate
                    ELSE tr.Estimate
                END
        END
    ) AS "Authorized",
    SUM(CASE WHEN cl_tr.classification = 'General' THEN tr.Estimate ELSE 0 END) AS "General"
FROM  
    TimeReports TR 
LEFT OUTER JOIN  Proposals p 
    ON tr.TimeWorkTypeID = p.ProposalCode
-- 关联Proposals对应的分类
LEFT OUTER JOIN CLASSIFICATION_lookup cl_p
    ON cl_p.Classification_Id = p.Proposal_ID
-- 关联TimeReports对应的分类
LEFT OUTER JOIN CLASSIFICATION_lookup cl_tr
    ON cl_tr.Classification_Id = tr.TimeWorkTypeID
WHERE  
    tr.Accepted = 'Y'  
    AND tr.epoch BETWEEN 100 AND 300 
GROUP BY  
    tr.epoch, tr.sourceID;

关键改动说明 #

1. JOIN替代子查询：通过两次LEFT JOIN分别关联CLASSIFICATION_lookup表，提前获取Proposals和TimeReports对应的classification值，彻底避免在SUM内嵌套子查询。
2. 简化CASE逻辑：直接使用JOIN得到的字段cl_p.classification和cl_tr.classification进行条件判断，完全符合SQL Server的语法要求。
3. 性能保障：JOIN操作可以利用CLASSIFICATION_lookup.Classification_Id上的索引，相比标量函数逐行执行的方式，能大幅提升百万级数据场景下的查询效率，适合定时数据复制任务。

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