MySQL计数表的列中的重复项 [英] MySql count duplicates in column of a table

问题描述

我有一个名为telco的表，该表有2列，分别为id(PK)和telco_prov.我想计算telco_prov列中的重复记录，然后显示它出现的次数.

I have a table named telco which has 2 columns, id(PK) and telco_prov. I want to count the duplicate records in the telco_prov column, then display the number of times it appeared.

telco表示例:

id    telco_prov
1     Smart
2     Smart
3     Globe
4     Globe
5     Globe

这是我的代码:

    $query = "select telco_prov, count(*) c from telco group by telco_prov having c > 1";
    $result = mysql_query($query) or die('Error: '.mysql_error ());
    $row = mysql_fetch_assoc($result);

    echo "SMART: <font color= 'red'>".$row['c']."</font>";
    echo "<br>GLOBE: <font color= 'red'>".$row['c']."</font>";

代码没有错误，但是没有正确计数.

The code has no error, but it doesn't do the counting correctly.

上面的代码的结果:

SMART: 2
GLOBE: 2

但是应该是:

SMART: 2
GLOBE: 3

我尝试在查询中添加where语句，但仍然无法正常工作.

I've tried adding where statement in the query but still it doesn't work.

$query = "select telco_prov, count(*) c from telco where telco_prov = 'Smart' group by telco_prov having c > 1";

非常感谢您的帮助.谢谢！

Your help would be much appreciated. Thanks!

推荐答案

您必须循环搜索结果.看到下面的代码

You have to loop the results. see the below code

$query = "select telco_prov, count(*) c from telco group by telco_prov having c > 1";
$result = mysql_query($query) or die('Error: '.mysql_error ());
while($row = $mysql_fetch_assoc($result)) {
    echo $row['telco_prov'].": <font color= 'red'>".$row['c']."</font>";
}

这篇关于MySQL计数表的列中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！