我正在尝试弄清楚如何使用PHP确定特定数字范围内的行总数。
我有一个简单的MySQL表,只有一列。该列包含数千行,每行包含一个介于 0 和 100 之间的数字。
我想出了如何使用array_count_values查找特定数字的行数,但我无法弄清楚如何找到范围的行数。
例如,60 到 80 之间有多少个数字?
这是我为查找单个值而组合在一起的代码。我应该添加什么代码来查找范围值?
$query = "SELECT numbers FROM table";
$result = mysqli_query($conn, $query) or die("Error in $query");
$types = array();
while(($row = mysqli_fetch_assoc($result))) {
$types[] = $row['numbers'];
}
$counts = array_count_values($types);
echo $counts['12'];
如果您需要在多个范围内计数,您可以使用UNION
,这样您就不必发送 5 个查询。
$query = "SELECT COUNT(numbers) FROM `table` WHERE numbers between 00 and 20
UNION ALL
SELECT COUNT(numbers) FROM `table` WHERE numbers between 20 and 40
UNION ALL
SELECT COUNT(numbers) FROM `table` WHERE numbers between 40 and 60
UNION ALL
SELECT COUNT(numbers) FROM `table` WHERE numbers between 60 and 80
UNION ALL
SELECT COUNT(numbers) FROM `table` WHERE numbers between 80 and 100";
您可以通过多种方式执行此操作。
简单方法(一次全表扫描)
SELECT SUM(IF(x BETWEEN 20 AND 30, 1, 0)) AS b_20_30,
SUM(...) AS b_31_40,
...
FROM tableName...
在表扫描时,将仅返回一行包含所有结果。
花哨的方式(不是真的推荐)
如果您可以想出一个规则来将间隔映射到单个数字,例如:
0...9 => interval i = 0
10...19 => interval i = 1 => the rule is "i = FLOOR(X/10)"
20...29 => interval i = 2
。而且您不需要扫描太多行,您可能会执行一些不太易于维护的操作,例如:
SELECT SUM(FLOOR(POW(100, FLOOR(x / 10)))) AS result FROM tableName;
在这里,值 25(介于 20 和 29 之间)将变为 2,总和将增加 1002。只要每组的行数不超过 100 行,最终结果将是幂的单声总和,如果你有 - 比如说 - 0 到 9 之间的 17 行,10 到 19 之间的 31 行,以及 20 到 29 之间的 74 行,你会得到一个"神奇的客厅技巧"答案
743117
从那里您可以按该顺序将行数恢复为 74,31,17。
使用 1000而不是 100 会产生74031017(并且有可能应对每组中多达 999 个数字)。
请注意,在 SELECT 中使用函数几乎可以保证您需要完整、缓慢的表扫描。
使用索引提高速度
但是我们可以通过明智地使用索引 WHERE 来摆脱表扫描并简化生成 - 这在性能方面与 UNION 相同,但结果更简单,因为它只有一行:
SELECT (SELECT COUNT(*) FROM tableName WHERE x BETWEEN ...) AS b_20_30,
(...)
; -- no table name on the outer query
这将需要多个子查询(每个间隔一个),但这些子查询都将在可用的情况下使用x
上的索引,这可以使整个查询非常快。你只需要
CREATE INDEX x_ndx ON tableName(x);
相同的索引将大大提高上述"简单"查询的性能,该查询将不再需要表扫描,而只需要更快的索引扫描。
使用 PHP 构建查询
假设我们将间隔指定为方便的数组,我们可以首先使用 PHP 生成查询。无需手动输入所有这些选择。
$intervals = [ [ 20, 30 ], [ 17, 25 ], ... ];
function queryFromIntervals(array $intervals) {
$index = 0;
return 'SELECT ' .
implode(',', array_map(
function($interval) use ($tableName, &$index) {
return "(SELECT COUNT(1) FROM {$tableName} WHERE x BETWEEN {$interval[0]} AND {$interval[1]}) AS b_" . ($index++);
},
$intervals
))
// . ", (SELECT COUNT(*) FROM {$tableName}) AS total"
. ';';
}
这将再次生成一行,其中包含名为b_0
、b_1
等的字段。它们将包含行数,其中 x 的值介于相应区间的边界之间(以$intervals
为单位)(区间可能重叠)。
因此,通过执行查询并检索名为$result
的元组,您可能会得到
[ 'b_0' => 133, 'b_1' => 29 ]
这意味着有 133 行,x 在 20 到 30 之间,29 行 x 在 17 到 25 之间。您可以在查询中添加最后一个总计字段(在上面的代码中注释):
, ... ( SELECT COUNT(*) FROM tableName ) AS total;
以同时获取总行数。
更改内部return
值的相同函数可以调整为生成使用 IF 而不是 SELECT 的"简单"查询。
为什么不让数据库为您完成繁重的工作呢?
$query = "SELECT COUNT(*) FROM table WHERE numbers BETWEEN 60 AND 80";
$result = mysqli_query($conn, $query) or die("Error in $query");
$row = mysqli_fetch_array($result, MYSQLI_NUM);
echo $row[0];
如果我理解得很好,你试着在你的请求sql中放一个条件。 看那 https://www.w3schools.com/sql/sql_where.asp
$query = "SELECT numbers FROM table WHERE numbers >= 60 AND numbers <= 80";