php 如何获取 GROUP BY 查询的总行数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6041886/
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
How to get the total number of rows of a GROUP BY query?
提问by Rudie
From the PDO manual:
从 PDO 手册：
PDOStatement::rowCount() returns the number of rowsaffected by the last DELETE, INSERT, or UPDATEstatement executed by the corresponding PDOStatement object.
If the last SQL statement executed by the associated PDOStatement was a SELECTstatement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databasesand should not be relied on for portable applications.
PDOStatement::rowCount() 返回 受相应 PDOStatement 对象执行的最后一个DELETE、INSERT 或 UPDATE语句影响 的行数。
如果关联的 PDOStatement 执行的最后一条 SQL 语句是 SELECT语句，则某些数据库可能会返回该语句返回的行数。但是，这种行为并不能保证适用于所有数据库，并且不应依赖于可移植应用程序。
I found that out only very recently. I had just changed my db abstraction layer to not use SELECT COUNT(1) ...anymore, because just quering the actual rows and then counting the result would be much more efficient. And now PDO doesn't support that!?
我是最近才发现的。我刚刚将我的数据库抽象层更改为不再使用SELECT COUNT(1) ...，因为仅查询实际行然后计算结果会更有效率。现在 PDO 不支持了！？
I don't use PDO for MySQL and PgSQL, but I do for SQLite. Is there a way (without completely changing the dbal back) to count rows like this in PDO? In MySQL, this would be something like this:
我不将 PDO 用于 MySQL 和 PgSQL，但我为 SQLite 使用。有没有办法（不完全改变 dbal 回来）在 PDO 中计算这样的行数？在 MySQL 中，这将是这样的：
$q = $db->query('SELECT a, b, c FROM tbl WHERE oele = 2 GROUP BY boele');
$rows = $q->num_rows;
// and now use $q to get actual data
With the MySQLi and PgSQL drivers, this is possible. With all PDO it isn't!?
使用 MySQLi 和 PgSQL 驱动程序，这是可能的。对于所有 PDO，它不是！？
PS. My initial solution was to extend the SQLResult->count method (my own) to replace SELECT ... FROMby SELECT COUNT(1) FROMand just return that number (very inefficient, but only for SQLite PDO). That's not good enough though, because in the example query above is a GROUP BY, which would change the meaning/function of the COUNT(1).
附注。我最初的解决方案是延长SQLResult->计数方法（我自己），以取代SELECT ... FROM由SELECT COUNT(1) FROM和刚刚返回（非常低效的，但只限于SQLite PDO）这个数字。但这还不够好，因为在上面的示例查询中是 a GROUP BY，它会改变COUNT(1).
采纳答案by Rudie
The method I ended up using is very simple:
我最终使用的方法非常简单：
$query = 'SELECT a, b, c FROM tbl WHERE oele = 2 GROUP BY boele';
$nrows = $db->query("SELECT COUNT(1) FROM ($query) x")->fetchColumn();
Might not be the most efficient, but it seems to be foolproof, because it actually counts the original query's results.
可能不是最有效的，但它似乎是万无一失的，因为它实际上计算了原始查询的结果。
回答by RoboTamer
Here is the solution for you
这是您的解决方案
$sql="SELECT count(*) FROM [tablename] WHERE key == ? ";
$sth = $this->db->prepare($sql);
$sth->execute(array($key));
$rows = $sth->fetch(PDO::FETCH_NUM);
echo $rows[0];
回答by mjec
It's a little memory-inefficient but if you're using the data anyway, I use this frequently:
这有点内存效率低下，但如果您无论如何都在使用数据，我会经常使用它：
$rows = $q->fetchAll();
$num_rows = count($rows);
回答by Denis de Bernardy
I don't use PDO for MySQL and PgSQL, but I do for SQLite. Is there a way (without completely changing the dbal back) to count rows like this in PDO?
我不将 PDO 用于 MySQL 和 PgSQL，但我为 SQLite 使用。有没有办法（不完全改变 dbal 回来）在 PDO 中计算这样的行数？
Accordingly to this comment, the SQLite issue was introduced by an API change in 3.x.
根据此评论，SQLite 问题是由 3.x 中的 API 更改引入的。
That said, you might want to inspect how PDO actually implements the functionality before using it.
也就是说，您可能想在使用 PDO 之前检查它实际是如何实现功能的。
I'm not familiar with its internals but I'd be suspicious at the idea that PDO parses your SQL (since an SQL syntax error would appear in the DB's logs) let alone tries to make the slightest sense of it in order to count rows using an optimal strategy.
我不熟悉它的内部结构，但我对 PDO 解析您的 SQL（因为 SQL 语法错误会出现在数据库的日志中）的想法持怀疑态度，更不用说试图对其进行最轻微的了解以计算行数使用最优策略。
Assuming it doesn't indeed, realistic strategies for it to return a count of all applicable rows in a select statement include string-manipulating the limit clause out of your SQL statement, and either of:
假设它确实没有，它返回 select 语句中所有适用行的计数的现实策略包括从 SQL 语句中对限制子句进行字符串操作，以及以下任一项：
- Running a select count() on it as a subquery (thus avoiding the issue you described in your PS);
- Opening a cursor, running fetch all and counting the rows; or
- Having opened such a cursor in the first place, and similarly counting the remaining rows.
- 在其上运行 select count() 作为子查询（从而避免您在 PS 中描述的问题）；
- 打开游标，运行 fetch all 并计算行数；或者
- 首先打开这样一个游标，并类似地计算剩余的行。
A much better way to count, however, would be to execute the fully optimized query that will do so. More often than not, this means rewriting meaningful chunks of the initial query you're trying to paginate -- stripping unneeded fields and order by operations, etc.
然而，更好的计数方法是执行完全优化的查询。通常情况下，这意味着重写您尝试分页的初始查询的有意义的块——剥离不需要的字段和按操作排序等。
Lastly, if your data sets are large enough that counts any kind of lag, you might also want to investigate returning the estimate derived from the statisticsinstead, and/or periodically caching the result in Memcache. At some point, having precisely correct counts is no longer useful...
最后，如果您的数据集足够大，可以计算任何类型的滞后，您可能还想研究返回从统计数据中得出的估计值，和/或定期将结果缓存在 Memcache 中。在某些时候，精确正确的计数不再有用......
回答by Nev Stokes
Keep in mind that a PDOStatementis Traversable. Given a query:
请记住， aPDOStatement是Traversable。给定一个查询：
$query = $dbh->query('
SELECT
*
FROM
test
');
It can be iterated over:
它可以迭代：
$it = new IteratorIterator($query);
echo '<p>', iterator_count($it), ' items</p>';
// Have to run the query again unfortunately
$query->execute();
foreach ($query as $row) {
echo '<p>', $row['title'], '</p>';
}
Or you can do something like this:
或者你可以做这样的事情：
$it = new IteratorIterator($query);
$it->rewind();
if ($it->valid()) {
do {
$row = $it->current();
echo '<p>', $row['title'], '</p>';
$it->next();
} while ($it->valid());
} else {
echo '<p>No results</p>';
}
回答by cwallenpoole
If you're willing to give up a hint of abstraction, then you could use a custom wrapper class which simply passes everything through to the PDO. Say, something like this: (Warning, code untested)
如果您愿意放弃一点抽象，那么您可以使用自定义包装类，该类简单地将所有内容传递给 PDO。说，像这样：（警告，代码未经测试）
class SQLitePDOWrapper
{
private $pdo;
public function __construct( $dns, $uname = null, $pwd = null, $opts = null )
{
$this->pdo = new PDO( $dns, $unam, $pwd, $opts );
}
public function __call( $nm, $args )
{
$ret = call_user_func_array( array( $this->pdo, $nm ), $args );
if( $ret instanceof PDOStatement )
{
return new StatementWrapper( $this, $ret, $args[ 0 ] );
// I'm pretty sure args[ 0 ] will always be your query,
// even when binding
}
return $ret;
}
}
class StatementWrapper
{
private $pdo; private $stat; private $query;
public function __construct( PDO $pdo, PDOStatement $stat, $query )
{
$this->pdo = $pdo;
$this->stat = $stat;
this->query = $query;
}
public function rowCount()
{
if( strtolower( substr( $this->query, 0, 6 ) ) == 'select' )
{
// replace the select columns with a simple 'count(*)
$res = $this->pdo->query(
'SELECT COUNT(*)' .
substr( $this->query,
strpos( strtolower( $this->query ), 'from' ) )
)->fetch( PDO::FETCH_NUM );
return $res[ 0 ];
}
return $this->stat->rowCount();
}
public function __call( $nm, $args )
{
return call_user_func_array( array( $this->stat, $nm ), $args );
}
}
回答by tradyblix
Maybe this will do the trick for you?
也许这对你有用？
$FoundRows = $DataObject->query('SELECT FOUND_ROWS() AS Count')->fetchColumn();
回答by Rahul Saxena
You have to use rowCount — Returns the number of rows affected by the last SQL statement
您必须使用 rowCount — 返回受最后一条 SQL 语句影响的行数
$query = $dbh->prepare("SELECT * FROM table_name");
$query->execute();
$count =$query->rowCount();
echo $count;
回答by XaviQV
What about putting the query results in an array, where you can do a count($array) and use the query resulting rows after? Example:
将查询结果放在一个数组中怎么样，您可以在其中执行 count($array) 并在之后使用查询结果行？例子：
$sc='SELECT * FROM comments';
$res=array();
foreach($db->query($sc) as $row){
$res[]=$row;
}
echo "num rows: ".count($res);
echo "Select output:";
foreach($res as $row){ echo $row['comment'];}
回答by Your Common Sense
That's yet another question, which, being wrongly put, spawns A LOT of terrible solutions, all making things awfully complicated to solve a non-existent problem.
这是另一个问题，如果放错了，会产生很多糟糕的解决方案，所有这些都使解决一个不存在的问题变得非常复杂。
The extremely simple and obvious rule for any database interaction is
任何数据库交互的极其简单和明显的规则是
Always select the only data you need.
始终选择您需要的唯一数据。
From this point of view, the question is wrong and the accepted answer is right. But other proposed solutions are just terrible.
从这个角度来看，问题是错误的，接受的答案是正确的。但是其他提出的解决方案很糟糕。
The question is "how to get the count wrong way". One should never answer it straightforward, but instead, the only proper answer is "One should never select the rows to count them. Instead, ALWAYS ask the database to count the rows for you." This rule is so obvious, that it's just improbable to see so many tries to break it.
问题是“如何以错误的方式进行计数”。永远不要直截了当地回答它，相反，唯一正确的答案是“永远不要选择行来计算它们。相反，始终要求数据库为您计算行数。” 这条规则是如此明显，以至于不可能看到这么多人试图打破它。
After learning this rule, we would see that this is an SQL question, not even PDO related. And, were it asked properly, from SQL perspective, the answer would appeared in an instant - DISTINCT.
学习这个规则后，我们会看到这是一个SQL 问题，甚至与 PDO 无关。而且，如果问得好，从 SQL 的角度来看，答案会立即出现 - DISTINCT.
$num = $db->query('SELECT count(distinct boele) FROM tbl WHERE oele = 2')->fetchColumn();
is the right answer to this particular question.
是这个特定问题的正确答案。
The opening poster's own solution is also acceptable from the perspective of the aforementioned rule, but would be less efficient in general terms.
从上述规则的角度来看，开场海报自己的解决方案也是可以接受的，但一般来说效率较低。
