MySQL HAVING 子句中的多个聚合函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14756222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple aggregate functions in HAVING clause
提问by blarg
Due to the nature of my query i have records with counts of 3 that would also fit the criteria of having count of 2 and so on. I was wondering is it possible to query 'having count more than x and less than 7' ? How could I write this. Here is my current code.
由于我的查询的性质，我有计数为 3 的记录，这些记录也符合计数为 2 的标准，依此类推。我想知道是否可以查询“计数大于 x 且小于 7”？我怎么会写这个。这是我当前的代码。
GROUP BY meetingID
HAVING COUNT( caseID )<4
I'd like something like
我想要类似的东西
GROUP BY meetingID
HAVING COUNT( caseID )<4 AND >2
That way it would only count for exactly 3
这样它只会计算正好 3
回答by Trent Earl
GROUP BY meetingID
HAVING COUNT(caseID) < 4 AND COUNT(caseID) > 2
回答by Taryn
There is no need to do two checks, why not just check for count = 3:
不需要做两次检查，为什么不只检查count = 3：
GROUP BY meetingID
HAVING COUNT(caseID) = 3
If you want to use the multiple checks, then you can use:
如果要使用多项检查，则可以使用：
GROUP BY meetingID
HAVING COUNT(caseID) > 2
AND COUNT(caseID) < 4
回答by lc.
For your example query, the only possible value greater than 2 and less than 4 is 3, so we simplify:
对于您的示例查询，大于 2 且小于 4 的唯一可能值是 3，因此我们简化：
GROUP BY meetingID
HAVING COUNT(caseID) = 3
In your general case:
在您的一般情况下：
GROUP BY meetingID
HAVING COUNT(caseID) > x AND COUNT(caseID) < 7
Or (possibly easier to read?),
或者（可能更容易阅读？），
GROUP BY meetingID
HAVING COUNT(caseID) BETWEEN x+1 AND 6
回答by Achrome
Something like this?
像这样的东西？
HAVING COUNT(caseID) > 2
AND COUNT(caseID) < 4
回答by Mukesh Kumar
Here I am writing full query which will clear your all doubts
在这里，我正在编写完整的查询，它将消除您的所有疑虑
SELECT BillingDate,
COUNT(*) AS BillingQty,
SUM(BillingTotal) AS BillingSum
FROM Billings
WHERE BillingDate BETWEEN '2002-05-01' AND '2002-05-31'
GROUP BY BillingDate
HAVING COUNT(*) > 1
AND SUM(BillingTotal) > 100
ORDER BY BillingDate DESC
回答by Mamun
select CUSTOMER_CODE,nvl(sum(decode(TRANSACTION_TYPE,'D',AMOUNT)),0)) DEBIT,nvl(sum(DECODE(TRANSACTION_TYPE,'C',AMOUNT)),0)) CREDIT,
nvl(sum(decode(TRANSACTION_TYPE,'D',AMOUNT)),0)) - nvl(sum(DECODE(TRANSACTION_TYPE,'C',AMOUNT)),0)) BALANCE from TRANSACTION
GROUP BY CUSTOMER_CODE
having nvl(sum(decode(TRANSACTION_TYPE,'D',AMOUNT)),0)) > 0
AND (nvl(sum(decode(TRANSACTION_TYPE,'D',AMOUNT)),0)) - nvl(sum(DECODE(TRANSACTION_TYPE,'C',AMOUNT)),0))) > 0
