在MySQL的同一个表中查找重复项

I have a table with two columns - artist, release_id

我有一个有两列的表 - artist，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

12 个解决方案

#1

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;

#2

SELECT id,artist,COUNT(*) FROM myTable
GROUP BY artist, release_id HAVING COUNT(*) > 1

#3

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

#4

SELECT row, COUNT(row) AS num FROM mytable GROUP BY row HAVING (num > 1);

#5

SELECT artist, release_id, count(*) no_of_records, group_concat(id) FROM table GROUP BY artist, release_id HAVING count(*) > 1;

SELECT artist，release_id，count（*）no_of_records，group_concat（id）FROM table GROUP BY artist，release_id HAVING count（*）> 1;

also adding group_concat(id) gets you all ids of the duplicates.

同时添加group_concat（id）可以获得重复项的所有ID。

#6

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

#7

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 from table in artist IN（从具有计数（ID）> 1的艺术家中选择表组中的艺术家）和release_id IN（通过具有count（release_id）的release_id从表组中选择release_id> 1）;

Will Fetch: ArtistX : 45677 ArtistX : 45677 ArtistY : 378798 ArtistY : 378798

Will Fetch：ArtistX：45677 ArtistX：45677 ArtistY：378798 ArtistY：378798

#8

SELECT id,artist,COUNT(id) as found FROM table GROUP by id HAVING found > 1

#9

SELECT artist, count(*) 
FROM tableName 
GROUP BY artist 
HAVING count(*) > 1;

#10

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;

#11

This method might not be great for your but if you ever want to get rid of duplicates and do this while making sure they are duplicates indeed you can try this:

这个方法对你来说可能不是很好，但如果你想要摆脱重复并在确保它们是重复的时候这样做，你可以试试这个：

duplicate your table1 into table2, for example like this:

将table1复制到table2中，例如：

CREATE TABLE table2 AS SELECT * FROM table1;

CREATE TABLE table2 AS SELECT * FROM table1;
add a new column to table1, for example name it kount

向table1添加一个新列，例如将其命名为kount
run a query (this assumes release_id should an unique column):

运行查询（这假设release_id应该是一个唯一的列）：

UPDATE table1 AS t1 SET t1.kount = (SELECT COUNT(*) FROM table2 AS t2 WHERE t1.release_id = t2.release_id)

UPDATE table1 AS t1 SET t1.kount =（SELECT COUNT（*）FROM table2 AS t2 WHERE t1.release_id = t2.release_id）
drop table table2

放表table2
use table1.kount to find your duplicates and remove them or something. Preferably in PHP/Python/Perl. This way you can, for example, make sure they are indeed duplicates and just have have same release_id. Same release_id might be given by accident and titles, years of publication etc. might be different. So just put your code here to filter the duplicates (pseudocode):

使用table1.kount查找重复项并删除它们。最好是PHP / Python / Perl。例如，通过这种方式，您可以确保它们确实是重复的，并且只具有相同的release_id。同样的release_id可能是偶然发生的，标题，出版年限等可能会有所不同。所以只需将您的代码放在此处以过滤重复项（伪代码）：

foreach (sql(SELECT * FROM table1 WHERE kount>1)) do //do something

foreach（sql（SELECT * FROM table1 WHERE kount> 1））做//做某事

#12

-1

If you have more unique column in one row, you can use this:

如果一行中有更多唯一列，则可以使用：

DELETE FROM table WHERE id in(
    SELECT x.id 
    FROM ( 
        SELECT *,count(id) cc FROM table group by col1,col2,col3... 
    ) x
    WHERE x.cc>1
)

#1