带有SUM（CASE SUBQUERY）的子查询聚合函数 [英] Subquery aggregate function with SUM(CASE SUBQUERY)

问题描述

执行查询时出现错误

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

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

代码：

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 中的c>不如 HAVING 中的那个那么重要，因此，即使有人可以帮助我，即使没有 SUM（...）在 SELECT 中会很有帮助。

SUM in SELECT is not that important as the one in HAVING, so if someone can help me out even without that SUM(...) in SELECT it would be helpful.

推荐答案

我认为将子查询移至 from 子句会更好。根据您的逻辑，您要查找 join s，而不是 left join s-毕竟，具有子句只是在寻找任何匹配项。

I think it would be better to move the subquery to the from clause. Based on your logic, you are looking for joins, not left joins -- after all, the having clause is simply looking for any match.

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

I think the following query does what you want:

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;

这篇关于带有SUM（CASE SUBQUERY）的子查询聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！