在MySQL中查找重复记录

可能不会非常高效，但这个方法应该能够工作：

SELECT *
FROM list AS outer
WHERE (SELECT COUNT(*)
        FROM list AS inner
        WHERE inner.address = outer.address) > 1;

这将在一个表传递中选择重复项，无需子查询。

SELECT  *
FROM    (
        SELECT  ao.*, (@r := @r + 1) AS rn
        FROM    (
                SELECT  @_address := 'N'
                ) vars,
                (
                SELECT  *
                FROM
                        list a
                ORDER BY
                        address, id
                ) ao
        WHERE   CASE WHEN @_address <> address THEN @r := 0 ELSE 0 END IS NOT NULL
                AND (@_address := address ) IS NOT NULL
        ) aoo
WHERE   rn > 1

这个查询实际上模拟了在Oracle和SQL Server中存在的ROW_NUMBER()函数。

有关详细信息，请参阅我的博客文章：

• Analytic functions: SUM, AVG, ROW_NUMBER - 在MySQL中进行模拟。

SELECT  `Language` , id, COUNT( id ) AS how_many
FROM  `languages` 
GROUP BY  `Language` 
HAVING how_many >=2
ORDER BY how_many DESC

SELECT id, count(*) as c  
 FROM 'list'
GROUP BY id HAVING c > 1

这将返回重复的id及其出现次数，如果没有重复的id，则不会返回任何内容。

更改group by中的id（例如：address），它将返回一个地址重复出现的次数，由第一个具有该地址的id确定。

SELECT id, count(*) as c  
 FROM 'list'
GROUP BY address HAVING c > 1

希望这有帮助。享受吧；)

select * from table_name t1 inner join (select distinct <attribute list> from table_name as temp)t2 where t1.attribute_name = t2.attribute_name

select * from list l1 inner join (select distinct address from list as list2)l2 where l1.address=l2.address

 SELECT firstname, lastname, address FROM list
 WHERE 
 Address in 
 (SELECT address FROM list
 GROUP BY address
 HAVING count(*) > 1)

就我个人而言，这个查询解决了我的问题：

SELECT `SUB_ID`, COUNT(SRV_KW_ID) as subscriptions FROM `SUB_SUBSCR` group by SUB_ID, SRV_KW_ID HAVING subscriptions > 1;

此脚本的作用是显示表格中存在多次的所有订阅者ID和找到的重复次数。

这是表格的列：

| SUB_SUBSCR_ID | int(11)     | NO   | PRI | NULL    | auto_increment |
| MSI_ALIAS     | varchar(64) | YES  | UNI | NULL    |                |
| SUB_ID        | int(11)     | NO   | MUL | NULL    |                |    
| SRV_KW_ID     | int(11)     | NO   | MUL | NULL    |                |

最快的重复项删除查询过程：

/* create temp table with one primary column id */
INSERT INTO temp(id) SELECT MIN(id) FROM list GROUP BY (isbn) HAVING COUNT(*)>1;
DELETE FROM list WHERE id IN (SELECT id FROM temp);
DELETE FROM temp;

SELECT t.*,(select count(*) from city as tt where tt.name=t.name) as count FROM `city` as t where (select count(*) from city as tt where tt.name=t.name) > 1 order by count desc

将城市替换为您的表。 将名称替换为您的字段名称。

这里大部分的答案没有处理当您有多个重复结果和/或需要检查多列重复时的情况。当您面临这种情况时，可以使用以下查询来获取所有重复的ID：

SELECT address, email, COUNT(*) AS QUANTITY_DUPLICATES, GROUP_CONCAT(id) AS ID_DUPLICATES
    FROM list
    GROUP BY address, email
    HAVING COUNT(*)>1;

如果您想将每个结果列为单独的行，则需要使用更复杂的查询。这是我发现可行的查询：

CREATE TEMPORARY TABLE IF NOT EXISTS temptable AS (    
    SELECT GROUP_CONCAT(id) AS ID_DUPLICATES
    FROM list
    GROUP BY address, email
    HAVING COUNT(*)>1
); 
SELECT d.* 
    FROM list AS d, temptable AS t 
    WHERE FIND_IN_SET(d.id, t.ID_DUPLICATES) 
    ORDER BY d.id;