sql - CASE WHEN THEN 无法对包含聚合或子查询的表达式执行聚合函数

Question

这是我的代码：

SELECT SUM 
        (CASE
            WHEN (dbo.EMBARQUE.EmbEst) = 0 THEN

                0
            WHEN (dbo.EMBARQUE.EmbEst) = 3 THEN 
                0
            WHEN (dbo.EMBARQUE.EmbEst) = 6 THEN 
                0       
            WHEN (dbo.EMBARQUE.EmbEst) = 7 THEN 
                CASE
                    WHEN (SELECT COUNT (dbo.CUMPLIDO.CumpCod) from dbo.CUMPLIDO where dbo.CUMPLIDO.EmbCod = dbo.EMBARQUE.EmbCod and dbo.CUMPLIDO.CumpVol = 0) > 0 THEN 
                        0
                    ELSE  
                        dbo.EMBARQUE.EmbVol
                END
            ELSE
                dbo.EMBARQUE.EmbVol
        END) FROM dbo.EMBARQUE

标签： sql

Answer 1

你里面有一个子查询sum()。这是不允许的。此版本使用子查询来计算您想要的标志，然后将过滤逻辑移至WHERE子句：

SELECT SUM(e.EmbVol)
FROM (SELECT e.*,
             (CASE WHEN EXISTS (SELECT 1 FROM dbo.CUMPLIDO c WHERE c.EmbCod = e.EmbCod AND c.CumpVol = 0)
                   THEN 1 ELSE 0
              END) as is_CumpVol_0
      FROM dbo.EMBARQUE e
      WHERE e.EmbEst NOT IN (0, 3, 6)
     ) e
WHERE e.EmbEst <> 7 OR is_CumpVol_0 = 0;