本文介绍了带有SUM(CASE 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 $ c $
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 join
s, not left join
s -- 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)的子查询聚合函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!