通过查询获取具有GROUP BY的行数

MySQL中有一个很好的解决方案。

将关键字SQL\u CALC\u FOUND\u行添加到关键字SELECT之后:

SELECT SQL_CALC_FOUND_ROWS t3.id, a,bunch,of,other,stuff FROM t1, t2, t3 
WHERE (associate t1,t2, and t3 with each other) 
GROUP BY t3.id 
LIMIT 10,20

之后,使用FOUND_ROWS()函数运行另一个查询:

SELECT FOUND_ROWS();

它应该返回没有LIMIT子句的行数。

有关详细信息,请签出此页面: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

SELECT
     COUNT(DISTINCT t3.id)
FROM...

SELECT
     COUNT(*)
FROM
     (
     <Your query here>
     ) AS SQ

我不使用MySQL,所以我不知道这些查询在那里是否有效。

使用子查询:

SELECT COUNT(*) FROM    
(
SELECT t3.id, a,bunch,of,other,stuff FROM t1, t2, t3 
WHERE (associate t1,t2, and t3 with each other) 
GROUP BY t3.id 
)    
as temp;

所以temp包含行数。

SELECT SQL_CALC_FOUND_ROWS t3.id, a,bunch,of,other,stuff 
FROM t1, t2, t3 
WHERE (associate t1,t2, and t3 with each other) 
GROUP BY t3.id 
LIMIT 10,20;

SELECT FOUND_ROWS(); -- for most recent query

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

所有给定的ans将执行查询,然后查找计数。在大数据集上,Distinct肯定比group by慢。

下面是查找分组计数的最佳方法

SELECT 
    sum(1) as counttotal
FROM (
    Your query with group by operator
) as T

这将在计算分组依据时查找计数。