在mysql上显示单个结果 - Display single result on mysql

Display single result on mysql

本文关键字：单个结果显示 mysql | 更新日期: 2024-02-06

如何使用此代码只得到一个结果？我在数我们能在表中找到每个"双"的次数。数据库连接并没有什么问题，结果只是得到了重复的值。非常感谢。

The output should be like
    DOUBLE       COUNT
    01 02          10 (as example)
    03 04          12 (as example)
    I am getting duplicated like...
    01 02          10 (as example)
    01 02          10 (as example)

<?php
if ($check == 'doubles'){
   sort($doubles);
   foreach ($doubles as $double) {
        explode(" ", $double);
        $dn1 = $double[0].$double[1];
        $dn2 = $double[2].$double[3];
if ($stmt = $post_con->prepare('SELECT DISTINCT FROM tb WHERE CONCAT(" ", res, " ") LIKE CONCAT("% ", ?, " %") AND CONCAT(" ", res, " ") LIKE CONCAT("% ", ?, " %")')) {
       $stmt->bind_param("ss", $dn1, $dn2);
       $dn1 = sprintf('%02d', $dn1);
       $dn2 = sprintf('%02d', $dn2);
       $stmt->execute();
       $stmt->store_result();
       $qty = $stmt->num_rows;
       echo '<div class="mini">'.$dn1.'</div>';
       echo '<div class="mini">'.$dn2.'</div>';
       echo '<div class="n-sort">'.$qty.'</div>';

        }

}
?>

很难完全匹配您的问题，因为我们不知道您的表结构（尤其是res字段内容），但我肯定认为这部分代码中存在问题：

explode(" ", $double);
$dn1 = $double[0].$double[1];
$dn2 = $double[2].$double[3];

当$double假定值为01 02时，可以在['01','02']中分解它，但不会捕获分解的数组。

然后，使用的语法与分解数组和原始字符串都不兼容。串联后，您会得到以下结果：

$double = '01 02';
# index >  01234
$dn1 = '01';
$dn2 = ' 0';

以这种方式替换原始代码：

$exploded = explode(" ", $double);
$dn1 = $exploded[0];
$dn2 = $exploded[1];

或者通过这种方式：

explode(" ", $double);
$dn1 = $double[0].$double[1];
$dn2 = $double[3].$double[4];

Using distinct will return all distinct rows in an entire table, but if there are multiple unique rows, then each row will be returned.

What you want is to use an aggregate function like COUNT(*) to count the number of rows that match your criteria.

SELECT 
    COUNT(*) 
FROM tb 
WHERE 
    CONCAT(" ", res, " ") LIKE CONCAT("% ", ?, " %") AND 
    CONCAT(" ", res, " ") LIKE CONCAT("% ", ?, " %")

这将返回与WHERE条件匹配的行数。

如果你想知道每个二重值在表中的次数，你可以按二重值分组，并对每组进行计数。例如：

SELECT 
    res,
    COUNT(*) 
FROM tb 
GROUP BY
    res

我很难回答这个问题，因为我不确定你的表中有哪些字段，res是什么，以及你感兴趣的双值在哪里发挥作用。

要计数重复项，请尝试使用以下查询：

SELECT count(1) FROM (
SELECT res, Count(1)
FROM tb 
WHERE CONCAT(" ", res, " ") LIKE CONCAT("% ", ?, " %") --I don't know did you need this conditions... 
AND CONCAT(" ", res, " ") LIKE CONCAT("% ", ?, " %")
GROUP BY res
HAVING Count(1) > 1)

编辑：
如果您尝试列出重复项，则：

SELECT res, Count(1)
FROM tb 
WHERE CONCAT(" ", res, " ") LIKE CONCAT("% ", ?, " %") --I don't know did you need this conditions... 
AND CONCAT(" ", res, " ") LIKE CONCAT("% ", ?, " %")
GROUP BY res
HAVING Count(1) > 1