MySQL 查詢重複的資料

我有這樣的一個表

我想查詢name重複資料有多少條，並且是哪個name重複了

SELECT `name`,COUNT(*) FROM tables GROUP BY `name` HAVING COUNT(*)>1;

我想要知道具體是那些資料重複了

SELECT * FROM tables WHERE `name` IN (SELECT `name` FROM tables GROUP BY `name` HAVING COUNT(*)>1) ORDER BY `name` ASC;

我想查出所有重複資料，並排除最小id

SELECT * FROM tables WHERE `name` IN (SELECT `name` FROM tables GROUP BY `name` HAVING COUNT(*)>1) AND id NOT IN (SELECT MIN(id) FROM tables GROUP BY `name` HAVING COUNT(*)>1) ORDER BY `name` ASC;

本作品採用《CC 協議》，轉載必須註明作者和本文連結