首页 文章

计算Mysql表中的重复记录？

提问于
浏览 次
20

我有 table ，下面的结构 .

TBL

id   name  
1    AAA
2    BBB
3    BBB
4    BBB
5    AAA
6    CCC

select count(name) c from tbl
group by name having c >1

返回此结果的查询：

AAA(2)  duplicate
BBB(3)  duplicate
CCC(1)  not duplicate

重复的名称为AAA和BBB . 最终结果，我想要的是这些重复记录的数量 .

结果应如下所示：重复产品总数（ 2 ）

mysql duplicates

6 回答

  • 4

    方法是使嵌套查询每个副本有一行，外部查询只返回内部查询结果的计数 .

    SELECT count(*) AS duplicate_count
FROM (
 SELECT name FROM tbl
 GROUP BY name HAVING COUNT(name) > 1
) AS t
    回复于
  • 5

    为什么不将它包装在子查询中：

    SELECT Count(*) TotalDups
FROM
(
    select Name, Count(*)
    from yourTable
    group by name
    having Count(*) > 1
) x

    见SQL Fiddle with Demo

    回复于
  • 9

    使用IF语句获得所需的输出：

    SELECT name, COUNT(*) AS times, IF (COUNT(*)>1,"duplicated", "not duplicated") AS duplicated FROM <MY_TABLE> GROUP BY name

    输出：

    AAA 2 duplicated
BBB 3 duplicated
CCC 1 not duplicated
    回复于
  • 32

    For List:

    SELECT COUNT(`name`) AS adet, name
FROM  `tbl` WHERE `status`=1 GROUP BY `name`
ORDER BY `adet`  DESC

    For Total Count:

    SELECT COUNT(*) AS Total
    FROM (SELECT COUNT(name) AS cou FROM tbl GROUP BY name HAVING cou>1 ) AS virtual_tbl

    //总计：5

    回复于
  • 0

    接受的答案 counts the number of rows that have duplicates ，而不是 amount of duplicates . 如果要计算 actual number of duplicates ，请使用：

    SELECT COALESCE(SUM(rows) - count(1), 0) as dupes FROM(

    SELECT COUNT(1) as rows
    FROM `yourtable`
    GROUP BY `name`
    HAVING rows > 1

) x

    这样做的总和是组中的重复项，但随后减去了具有重复项的记录数量 . 原因是group by total不是全部重复，每个分组的一个记录是唯一的行 .

    小提琴：http://sqlfiddle.com/#!2/29639a/3

    回复于
  • 10

    SQL代码是：

    SELECT VERSION_ID, PROJECT_ID, VERSION_NO, COUNT(VERSION_NO) AS dup_cnt
FROM MOVEMENTS
GROUP BY VERSION_NO
HAVING (dup_cnt > 1 && PROJECT_ID = 11660)
    回复于

相关问题