选择相同的列值以字符串开头，2个问题的行 [英] select rows where same column values start with string, 2 questions

问题描述

我正在尝试获取多个行总和，每个总和具有匹配的列字符串值，并且所有值均具有匹配的4个字符前缀，但是它不起作用，我可以寻求帮助.另外，CASE会成为数据库上或多或少昂贵的查询吗? 从此开始:

I'm trying to get multiple sums of rows, each sum has a matching column string value, and all the values have a matching 4 character prefix, but it's not working and I could use some help. Also, would CASE be a more or less costly query on the db? Started with this:

$sql = 
"SELECT col1
FROM table
WHERE substr(col1,1,5)='$string'";
$query = mysqli_query($con, $sql);
$row = mysqli_fetch_array($query,MYSQLI_ASSOC);
$results = $row['col1'];
$sum1 = count(array_keys($results,'string1'));
$sum2 = count(array_keys($results,'string2'));

从WHERE子句中的同一列获取结果是否可行?

Does it work to get the results from the same column that's in the WHERE clause?

在实践中，col1具有像aaaa_string1，aaaa_string1，aaaa_string2，aaaa_string2，bbbb_string8这样的行值，因此我想获取所有具有aaaa的数组的col1结果，然后随后过滤每个string1存在多少个和string2.

In practice, col1 has row values like aaaa_string1, aaaa_string1, aaaa_string2, aaaa_string2, bbbb_string8... so I'm looking to get all col1 results that have the aaaa in an array, then subsequently filter how many exist of each string1 and string2.

推荐答案

使用SUBSTR(col1, LENGTH('$string')+1)获取前缀后面的列部分，并以此分组.

Use SUBSTR(col1, LENGTH('$string')+1) to get the part of the column after the prefix, and group by this.

使用LIKE 'prefix%'匹配以前缀开头的列.

Use LIKE 'prefix%' to match a column beginning with a prefix.

SELECT SUBSTR(col1, LENGTH('$string')+1) AS suffix, COUNT(*) as count
FROM table
WHERE col1 LIKE '$string%'
GROUP BY suffix

然后，您可以使用循环创建具有所有计数的关联数组:

Then you can use a loop to create an associative array with all the counts:

$counts = array();
while ($row = mysqli_fetch_assoc($query) {
    $counts[$row['suffix']] = $row['count'];
}
var_dump($counts);

这篇关于选择相同的列值以字符串开头，2个问题的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！