admin

具有计数和具有的SQL计数记录

sql

我在以简单的查询方式对记录进行计数时遇到了问题:

SELECT req_ownerid, count(req_status_lender) AS total6 
FROM bor_requests
WHERE (req_status_lender = 0 AND req_status_borrower = 0) OR 
      (req_status_lender = 1 AND req_status_borrower = 1)
GROUP BY req_ownerid 
HAVING req_ownerid = 70

我认为这将计算所有记录(req_status_lender = 0 AND req_status_borrower =
0)和(req_status_lender = 1 AND req_status_borrower =
1),然后将总数提供给我,但只给我其中一个总数(req_status_lender = 0 AND req_status_borrower =
0)或(req_status_lender = 1 AND req_status_borrower = 1)。

有什么想法我做错了吗?


阅读 261

收藏
2021-07-01

共1个答案

admin

您应该HAVING仅使用该子句来限制上面查询中已聚合的内容-
例如,如果您要选择aSUM(....)COUNT(...)大于5的所有那些行,则可以使用HAVING SUM(...) > 5

您在这里所做的是一个标准WHERE子句-在其中添加它!

SELECT req_ownerid, count(req_status_lender) AS total6 
FROM bor_requests
WHERE req_ownerid = 70
      AND ((req_status_lender = 0 AND req_status_borrower = 0) OR 
           (req_status_lender = 1 AND req_status_borrower = 1))
GROUP BY req_ownerid
2021-07-01