如何获取按列分组的行数？ [英] How to get the number of rows grouped by column?

问题描述

从PDO手册：

PDOStatement :: rowCount（）返回
受到相应的
PDOStatement对象执行的最后
DELETE，INSERT或UPDATE 语句
的影响。

PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

如果
执行的最后一条SQL语句是关联的PDOStatement是
SELECT 语句，一些数据库可能
返回返回的行数由
该语句。但是，此
行为不能保证所有
数据库，对于便携式应用程序不应依赖
。

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by that statement. However, this behaviour is not guaranteed for all databases and should not be relied on for portable applications.

我发现只是最近才出现。我刚刚改变了我的db抽象层，不再使用 SELECT COUNT（1）... ，因为只是查询实际的行，然后计数结果会更加高效。现在PDO不支持那个！？

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!?

我不使用PDO for MySQL和Pg​​SQL，但我做SQLite。有没有办法（没有完全改变dbal回来）在PDO中这样计数行？在MySQL中，这将是这样的：

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:

$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

使用MySQLi和PgSQL驱动程序， 这个有可能。对于所有PDO，它不是！？

With the MySQLi and PgSQL drivers, this is possible. With all PDO it isn't!?

PS。我的初始解决方案是扩展SQLResult-> count方法（我自己），用 SELECT ... FROM 替换 SELECT COUNT（1） / code>并返回该数字（非常低效，但只适用于SQLite PDO）。这是不够好，虽然，因为在上面的示例查询是一个 GROUP BY ，这将改变 COUNT（1）。

PS. My initial solution was to extend the SQLResult->count method (my own) to replace SELECT ... FROM by SELECT COUNT(1) FROM and 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).

推荐答案

我最后使用的方法非常简单：

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.

这篇关于如何获取按列分组的行数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！