带有SUM（CASE SUBQUERY）的子查询聚合函数

zhuber 发表于 Dev

zhuber

执行查询时出现错误

无法对包含聚合或子查询的表达式执行聚合功能。

代码：

SELECT 
    S.id,
    SUM(CASE WHEN sc.coverage IN (SELECT number FROM ArrayOfIntegersFromString(@dynamicData)) THEN 1 ELSE 0 END) as sm
FROM 
    Storefronts s
    LEFT JOIN StorefrontCoverages sc ON s.id = sc.storefront
    LEFT JOIN Vendors v ON s.vendor = v.Id
WHERE
(
    v.active = 1
    AND
    s.approved = 1
    AND
    s.status = 1 
)
GROUP BY S.id
HAVING SUM(CASE WHEN sc.coverage IN (SELECT number FROM ArrayOfIntegersFromString(@dynamicData)) THEN 1 ELSE 0 END)  > 0
ORDER BY sm desc

SUM在SELECT并不重要的之一HAVING，所以如果有人可以帮助我即使没有SUM(...)在SELECT这将是有益的。

戈登·利诺夫（Gordon Linoff）

我认为将子查询移到from子句会更好。根据您的逻辑，您正在寻找joins而不是left joins-毕竟，该having子句只是在寻找任何匹配项。

我认为以下查询可满足您的需求：

SELECT S.id, COUNT(dd.number) as sm
FROM Storefronts s JOIN
     StorefrontCoverages sc
     ON s.id = sc.storefront JOIN
     (SELECT number FROM ArrayOfIntegersFromString(@dynamicData)
     ) dd(number)
     ON sc.coverage = dd.number
WHERE s.approved = 1 AND s.status = 1 AND
      EXISTS (SELECT 1
              FROM Vendors v
              WHERE s.vendor = v.Id AND
                    v.active = 1
             )
GROUP BY S.id
ORDER BY sm desc;

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。