在 MySQL 中的同一个表中查找重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9239762/
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
Find duplicates in the same table in MySQL
提问by Franco
I have a table with two columns - artist, release_id
我有一个包含两列的表 - 艺术家，release_id
What query can I run to show duplicate records?
我可以运行什么查询来显示重复记录？
e.g. my table is
例如我的桌子是
ArtistX : 45677
ArtistY : 378798
ArtistX : 45677
ArtistZ : 123456
ArtistY : 888888
ArtistX : 2312
ArtistY: 378798
The query should show
查询应显示
ArtistX : 45677
ArtistX : 45677
ArtistY : 378798
ArtistY : 378798
回答by a'r
You can use a grouping across the columns of interest to work out if there are duplicates.
您可以对感兴趣的列使用分组来确定是否存在重复项。
SELECT
artist, release_id, count(*) no_of_records
FROM table
GROUP BY artist, release_id
HAVING count(*) > 1;
回答by DonCallisto
SELECT id,artist,COUNT(*) FROM myTable
GROUP BY artist, release_id HAVING COUNT(*) > 1
回答by Shiva Kumar
select * from table where artist IN (select artist from table group by artist having count(ID)>1) and release_id IN (select release_id from table group by release_id having count(release_id)>1);
select * from table where Artist IN（从表组中选择艺术家有 count(ID)>1）和 release_id IN（从表组中选择 release_id 有 count(release_id)>1）；
Will Fetch: ArtistX : 45677 ArtistX : 45677 ArtistY : 378798 ArtistY : 378798
将获取：ArtistX：45677 ArtistX：45677 ArtistY：378798 ArtistY：378798
回答by oezi
you can try something like this
你可以试试这样的
select artist, count(*) from mytable group by artist having count(*) > 1;
wich would output
将输出
artist count(*)
45677 2
378798 2
回答by dev009
SELECT artist, release_id, count(*) no_of_records, group_concat(id) FROM table GROUP BY artist, release_id HAVING count(*) > 1;
SELECT 艺术家，release_id，count(*) no_of_records，group_concat(id) FROM table GROUP BY 艺术家，release_id HAVING count(*) > 1;
also adding group_concat(id) gets you all ids of the duplicates.
还添加 group_concat(id) 为您提供重复项的所有 ID。
回答by Laurent Grégtheitroade
SELECT row, COUNT(row) AS num FROM mytable GROUP BY row HAVING (num > 1);
回答by Sarthak Y. Vaidya
you can use this query for the same result. it works for me
您可以将此查询用于相同的结果。这个对我有用
SELECT firstname, lastname, list.address FROM list INNER JOIN (SELECT address FROM list GROUP BY address HAVING count(id) > 1) dup ON list.address = dup.address
SELECT firstname, lastname, list.address FROM list INNER JOIN (SELECT address FROM list GROUP BY address HAVING count(id) > 1) dup ON list.address = dup.address
回答by Stelian Matei
SELECT id,artist,COUNT(id) as found FROM table GROUP by id HAVING found > 1
回答by John Woo
SELECT artist, count(*)
FROM tableName
GROUP BY artist
HAVING count(*) > 1;
回答by Jayy
Try this:
尝试这个：
SELECT A.ARTIST,A.RELEASE_ID FROM ARTISTS A
WHERE EXISTS(
SELECT 'X' FROM ARTISTS B
WHERE B.ARTIST = A.ARTIST AND B.RELEASE_ID = A.RELEASE_ID
GROUP BY B.ARTIST,B.RELEASE_ID
HAVING COUNT(B.ARTIST)>1)
ORDER BY A.ARTIST;
