在MySQL中查找重复的记录

我想在MySQL数据库中取出重复的记录。这可以用:

SELECT address, count(id) as cnt FROM list
GROUP BY address HAVING cnt > 1

结果是:

100 MAIN ST    2

我想要拖动它，以便它显示复制的每一行。喜欢的东西:

JIM    JONES    100 MAIN ST
JOHN   SMITH    100 MAIN ST

有什么想法可以做到吗?我试图避免做第一个，然后在代码中用第二个查询查找重复。

当前回答

这将在一次表传递中选择重复项，没有子查询。

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()

详见我博客上的文章:

分析函数:SUM, AVG, ROW_NUMBER -在MySQL模拟。

2009-05-12 18:36:31

其他回答

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

这将返回id和该id重复的次数，或者什么都没有，在这种情况下，您将不会有重复的id。

通过(例如:address)更改组中的id，它将返回一个地址由第一个找到的id与该地址重复标识的次数。

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

我希望这能有所帮助。喜欢。)

2022-05-18 10:10:13

这还将显示有多少重复项，并将在没有连接的情况下对结果进行排序

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

2015-03-17 10:28:46

会是这样的:

SELECT  t1.firstname t1.lastname t1.address FROM list  t1
    INNER JOIN  list t2 
    WHERE 
        t1.id < t2.id AND 
        t1.address = t2.address;

2022-07-28 01:09:59

关键是重写这个查询，以便它可以用作子查询。

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;

2009-05-12 18:33:40