如何在WHERE子句中添加SUM聚合筛选？SQL报错求助

原可正常运行的SQL查询：

SELECT TOP 1 
    P.*,  
    ProductDetails = (SELECT 
                          D.*, 
                          ProductColors = (SELECT
                                               C.*,
                                               IsValid = CAST(CASE WHEN ISNULL(AC.Reference_Descriptor, '') <> '' OR (W.Reference_ID = '' AND W.Reference_Descriptor = '') THEN 1 ELSE 0 END AS BIT)
                                           FROM ProductColors C
                                           LEFT JOIN AvailableColors AC ON AC.Reference_ID = C.Reference_ID AND AC.Mat_Type = C.Mat_Type
                                           WHERE C.colorProductID = D.detailproductID
                                           FOR JSON AUTO)
                      FROM ProductDetails D 
                      WHERE D.detailproductID = P.productID
                      FOR JSON AUTO)
FROM 
    Products P
WHERE 
    P.productStatus IN ('Backorder', 'Expired')
    AND P.ProductType = 'BATH'
    AND P.SupplierID = 'S-1205'         
ORDER BY 
    productID DESC

需添加筛选逻辑：仅返回对应ProductDetails表中Cost字段求和大于0的产品。尝试在WHERE子句末尾添加以下语句后执行报错：

AND SUM((SELECT D.Cost FROM ProductsDetails D 
         WHERE detailproductID = P.productID)) > 0

报错信息：

Cannot perform an aggregate function on an expression containing an aggregate or a subquery

最简实现方法 #

以下是两种简洁的修正方案，均能实现需求：

方法1：用EXISTS子查询（改动最小） #

直接在WHERE子句中添加EXISTS判断，检查当前产品对应的ProductDetails成本总和是否大于0：

SELECT TOP 1 
    P.*,  
    ProductDetails = (SELECT 
                          D.*, 
                          ProductColors = (SELECT
                                               C.*,
                                               IsValid = CAST(CASE WHEN ISNULL(AC.Reference_Descriptor, '') <> '' OR (W.Reference_ID = '' AND W.Reference_Descriptor = '') THEN 1 ELSE 0 END AS BIT)
                                           FROM ProductColors C
                                           LEFT JOIN AvailableColors AC ON AC.Reference_ID = C.Reference_ID AND AC.Mat_Type = C.Mat_Type
                                           WHERE C.colorProductID = D.detailproductID
                                           FOR JSON AUTO)
                      FROM ProductDetails D 
                      WHERE D.detailproductID = P.productID
                      FOR JSON AUTO)
FROM 
    Products P
WHERE 
    P.productStatus IN ('Backorder', 'Expired')
    AND P.ProductType = 'BATH'
    AND P.SupplierID = 'S-1205'
    -- 新增筛选条件
    AND EXISTS (
        SELECT 1
        FROM ProductDetails D
        WHERE D.detailproductID = P.productID
        GROUP BY D.detailproductID
        HAVING SUM(D.Cost) > 0
    )
ORDER BY 
    productID DESC

方法2：用JOIN预计算成本总和 #

先通过子查询计算每个产品的成本总和，再通过JOIN过滤：

SELECT TOP 1 
    P.*,  
    ProductDetails = (SELECT 
                          D.*, 
                          ProductColors = (SELECT
                                               C.*,
                                               IsValid = CAST(CASE WHEN ISNULL(AC.Reference_Descriptor, '') <> '' OR (W.Reference_ID = '' AND W.Reference_Descriptor = '') THEN 1 ELSE 0 END AS BIT)
                                           FROM ProductColors C
                                           LEFT JOIN AvailableColors AC ON AC.Reference_ID = C.Reference_ID AND AC.Mat_Type = C.Mat_Type
                                           WHERE C.colorProductID = D.detailproductID
                                           FOR JSON AUTO)
                      FROM ProductDetails D 
                      WHERE D.detailproductID = P.productID
                      FOR JSON AUTO)
FROM 
    Products P
-- 关联预计算成本总和的子查询
JOIN (
    SELECT detailproductID, SUM(Cost) AS TotalCost
    FROM ProductDetails
    GROUP BY detailproductID
    HAVING SUM(Cost) > 0
) D_Summary ON D_Summary.detailproductID = P.productID
WHERE 
    P.productStatus IN ('Backorder', 'Expired')
    AND P.ProductType = 'BATH'
    AND P.SupplierID = 'S-1205'         
ORDER BY 
    productID DESC

注：原错误语句中的ProductsDetails是笔误，正确表名为ProductDetails，已在方案中修正。

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