计算Mysql表中的重复记录？

I have table with, folowing structure.

我有桌子，下面的结构。

tbl

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

The query returning this result:

返回此结果的查询：

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

The names who are duplicates as AAA and BBB. The final result, who I want is count of this duplicate records.

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

Result should be like this: Total duplicate products (2)

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

6 个解决方案

#1

The approach is to have a nested query that has one line per duplicate, and an outer query returning just the count of the results of the inner query.

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

SELECT count(*) AS duplicate_count
FROM (
 SELECT name FROM tbl
 GROUP BY name HAVING COUNT(name) > 1
) AS t

#2

why not just wrap this in a sub-query:

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

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

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

#3

Use IF statement to get your desired output:

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

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

Output:

输出：

AAA 2 duplicated
BBB 3 duplicated
CCC 1 not duplicated

#4

The accepted answer counts the number of rows that have duplicates, not the amount of duplicates. If you want to count the actual number of duplicates, use this:

接受的答案计算具有重复项的行数，而不是重复项的数量。如果要计算重复的实际数量，请使用：

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

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

) x

What this does is total the duplicates in the group by, but then subtracts the amount of records that have duplicates. The reason is the group by total is not all duplicates, one record of each of those groupings is the unique row.

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

Fiddle: http://sqlfiddle.com/#!2/29639a/3

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

#5

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

// Total: 5

//总计：5

#6

SQL code is:

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)

#1