问题描述
我对
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
我想知道该查询返回的所有行中都没有LIMIT(因此我可以显示分页信息).
I want to know to many total rows this query would return without the LIMIT (so I can show pagination information).
通常,我会使用以下查询:
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
但是GROUP BY改变了COUNT的含义,取而代之的是,我得到一组代表每个组中唯一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时,是否可以获取行总数的计数?我想避免不得不执行整个查询并只计算行数,因为由于分页值,我只需要行的子集.我正在使用MySQL 5,但我认为这很通用.
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.
推荐答案
MySQL中有一个不错的解决方案.
There is a nice solution in MySQL.
在关键字SELECT之后紧接着添加关键字SQL_CALC_FOUND_ROWS:
Add the keyword SQL_CALC_FOUND_ROWS right after the keyword 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()函数运行另一个查询:
After that, run another query with the function FOUND_ROWS() :
SELECT FOUND_ROWS();
它应该返回不带LIMIT子句的行数.
It should return the number of rows without the LIMIT clause.
查看此页面以获取更多信息: http ://dev.mysql.com/doc/refman/5.0/zh-CN/information-functions.html#function_found-rows
Checkout this page for more information : http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
这篇关于使用GROUP BY查询获取行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!