我想在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 date FROM logs group by date having count(*) >= 2

其他回答

不是很有效,但应该可以工作:

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

我试着用最好的答案来回答这个问题,但它还是把我弄糊涂了。实际上,我只需要在表的一个字段上。下面这个链接的例子对我来说效果很好:

SELECT COUNT(*) c,title FROM `data` GROUP BY title HAVING c > 1;

从列表中选择地址where address = any (Select address from (Select address, count(id) CNT from list group by address with CNT > 1) as t1)按地址排序

内部子查询返回具有重复地址的行 外层子查询返回重复地址的地址列。 外层子查询必须只返回一列,因为它被用作操作符'= any'的操作数。

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

我使用以下方法:

SELECT * FROM mytable
WHERE id IN (
  SELECT id FROM mytable
  GROUP BY column1, column2, column3
  HAVING count(*) > 1
)