MySQL 使用 GROUP BY 查询获取行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/364825/
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
Getting the number of rows with a GROUP BY query
提问by SoapBox
I have a query to the effect of
我有一个关于效果的查询
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
LIMIT 10,20
I want to know to many total rows this query would return without the LIMIT (so I can show pagination information).
我想知道这个查询在没有 LIMIT 的情况下返回的总行数（所以我可以显示分页信息）。
Normally, I would use this query:
通常，我会使用这个查询：
SELECT COUNT(t3.id) FROM t1, t2, t3
WHERE (associate t1,t2, and t3 with each other)
GROUP BY t3.id
However the GROUP BY changes the meaning of the COUNT, and instead I get a set of rows representing the number of unique t3.id values in each group.
然而 GROUP BY 改变了 COUNT 的含义，相反，我得到了一组代表每个组中唯一 t3.id 值数量的行。
Is there a way to get a count for the total number of rows when I use a GROUP BY? I'd like to avoid having to execute the entire query and just counting the number of rows, since I only need a subset of the rows because the values are paginated. I'm using MySQL 5, but I think this pretty generic.
当我使用 GROUP BY 时，有没有办法计算总行数？我想避免执行整个查询而只计算行数，因为我只需要行的一个子集，因为值是分页的。我正在使用 MySQL 5，但我认为这非常通用。
采纳答案by Sylvain
There is a nice solution in MySQL.
MySQL中有一个很好的解决方案。
Add the keyword SQL_CALC_FOUND_ROWS right after the keyword SELECT :
在关键字 SELECT 之后添加关键字 SQL_CALC_FOUND_ROWS ：
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
After that, run another query with the function FOUND_ROWS() :
之后，使用函数 FOUND_ROWS() 运行另一个查询：
SELECT FOUND_ROWS();
It should return the number of rows without the LIMIT clause.
它应该返回没有 LIMIT 子句的行数。
Checkout this page for more information : http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
查看此页面了解更多信息：http: //dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
回答by Tom H
Are the "bunch of other stuff" all aggregates? I'm assuming so since your GROUP BY only has t3.id. If that's the case then this should work:
“一堆其他东西”都是聚合体吗？我假设如此，因为您的 GROUP BY 只有 t3.id。如果是这种情况，那么这应该有效：
SELECT
COUNT(DISTINCT t3.id)
FROM...
The other option of course is:
另一个选择当然是：
SELECT
COUNT(*)
FROM
(
<Your query here>
) AS SQ
I don't use MySQL, so I don't know if these queries will work there or not.
我不使用 MySQL，所以我不知道这些查询是否可以在那里工作。
回答by Silver Moon
Using sub queries :
使用子查询：
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;
so temp contains the count of rows.
所以 temp 包含行数。
回答by Bill Karwin
You're using MySQL, so you can use their function to do exactly this.
您正在使用 MySQL，因此您可以使用它们的功能来完成此操作。
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
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
回答by Abhishek Goel
All ans given will execute the query and then find the count. Distinct is definitely slower than group by on large dataset.
给出的所有答案都将执行查询，然后找到计数。在大型数据集上，Distinct 肯定比 group by 慢。
Best way to find the count of group by is below
查找分组计数的最佳方法如下
SELECT
sum(1) as counttotal
FROM (
Your query with group by operator
) as T
This will find the count while calculating group by.
这将在计算 group by 时找到计数。
