我想在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
有什么想法可以做到吗?我试图避免做第一个，然后在代码中用第二个查询查找重复。
当前回答
最快重复项删除查询过程:
/* 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 *
FROM (SELECT address, COUNT(id) AS cnt
FROM list
GROUP BY address
HAVING ( COUNT(id) > 1 ))
最快重复项删除查询过程:
/* 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 t1.firstname t1.lastname t1.address FROM list t1
INNER JOIN list t2
WHERE
t1.id < t2.id AND
t1.address = t2.address;
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
我希望这能有所帮助。喜欢。)
不是很有效，但应该可以工作:
SELECT *
FROM list AS outer
WHERE (SELECT COUNT(*)
FROM list AS inner
WHERE inner.address = outer.address) > 1;
