结果 "greater than or equal to"的 SQL COUNT 函数

Question

我目前有一个查询，它返回每个客户持有的帐户总数，但我如何使它只返回拥有超过 1 个帐户的客户？

SELECT C.customerID, COUNT(O.accNumber) AS "total"
FROM Customer C, Owns O
WHERE C.customerID = O.customerID
GROUP BY C.customerID

Answer 1

您的问题的答案是HAVING .但是，您需要学会使用正确的JOIN句法。简单规则:切勿在 FROM 中使用逗号条款。始终使用显式 JOIN句法。

SELECT C.customerID, COUNT(O.accNumber) AS total
FROM Customer C JOIN
     Owns O
     ON C.customerID = O.customerID
GROUP BY C.customerID
HAVING COUNT(*) > 1;

实际上，您甚至不需要 JOIN :

SELECT o.customerID, COUNT(o.accNumber) AS total
FROM Owns o
GROUP BY o.customerID
HAVING COUNT(*) > 1;

这要简单得多。