MySQL 计算Mysql表中的重复记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12528644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count duplicates records in Mysql table?
提问by dido
I have table with, folowing structure.
我有一张桌子，下面的结构。
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)
回答by xdazz
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
回答by arutaku
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
回答by Taryn
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
回答by Limitless isa
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
回答by Anthony Vipond
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.
这样做是将组中的重复项总计，然后减去具有重复项的记录数量。原因是按总数分组并不是全部重复，每个分组的一个记录是唯一的行。
Fiddle: http://sqlfiddle.com/#!2/29639a/3
小提琴：http://sqlfiddle.com/#!2/29639a/3
回答by Rankit Ranjan
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)
