我正在尝试下面的MySql查询-
SELECT count(*), bug_id,
bug_status,
resolution,
short_desc,
DATE_FORMAT(deadline, "%m/%d/%Y")AS deadline
FROM bugs
WHERE ( bug_status IN ( 'RESOLVED', 'VERIFIED', 'INTEST' )
AND deadline BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
)
OR ( bug_status IN ( 'RESOLVED', 'VERIFIED', 'INTEST' )
AND deadline BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND
CURDATE() )
ORDER BY deadline ASC但是它只返回一个列的结果,而不是全部96个条目。

还有什么更好的办法,让我先数一数,再算出所有的结果?
发布于 2015-01-27 18:22:17
如果您想要每一行中的全部计数,那么在MySQL中就有点难看了。你基本上有两个选择:
下面是如何将它们放在MySQL中的方法--注意如何重复您的WHERE逻辑:
SELECT
CountOnly.RowCount,
bug_status,
resolution,
short_desc,
DATE_FORMAT(deadline, '%m/%d/%Y') AS deadline
FROM bugs
INNER JOIN (
SELECT COUNT(*) AS RowCount
FROM bugs
WHERE
( bug_status IN ( 'RESOLVED', 'VERIFIED', 'INTEST' )
AND deadline BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
)
OR ( bug_status IN ( 'RESOLVED', 'VERIFIED', 'INTEST' )
AND deadline BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE() )
) CountOnly ON 1 = 1
WHERE
( bug_status IN ( 'RESOLVED', 'VERIFIED', 'INTEST' )
AND deadline BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
)
OR ( bug_status IN ( 'RESOLVED', 'VERIFIED', 'INTEST' )
AND deadline BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND CURDATE() )发布于 2015-01-27 18:21:59
COUNT() (或任何其他聚合函数)出现在SELECT子句中,就会将查询转化为聚合查询。如果没有GROUP BY子句,聚合查询必然只能返回一行。
如果必须在每一行中返回一个count项,则必须在一个联合的单行子查询中获得它。像这样的东西会起作用的。
SELECT b.cnt, a.*
FROM table a
JOIN (SELECT COUNT(*) cnt FROM table) b ON 1=1但是对于您拥有的大查询,您必须在我提到的table中重复两次。
https://stackoverflow.com/questions/28177016
复制相似问题