mysql delete语句删除指定重复值的数据只留一条

[qlkj666]

mysql_query("delete from member where username='arut'");

如上可以删除username='arut'的所有数据，

想删除username='arut'的所有重复相同数据，但要保留第一条，就是ID值最小的，怎么写？谢谢！

[xuzuning]

虽然 delete 支持 limit 子句，但你却不能知道有多少重复 不如 对 username 做唯一索引后 Copy 一下

[qlkj666]

引用 1 楼 xuzuning 的回复:

虽然 delete 支持 limit 子句，但你却不能知道有多少重复 不如 对 username 做唯一索引后 Copy 一下

就是不知道有多少重复，但只想保留ID最小的第一条，能不能mysql_query("delete from member where 怎么来实现？

[徐卡丘]

删除表中多余的重复记录，重复记录是根据单个字段（username）来判断，只留有userid最小的记录: DELETE FROM member WHERE username IN ( SELECT username FROM member GROUP BY username HAVING count(username) > 1 ) AND userid NOT IN ( SELECT min(userid) FROM member GROUP BY username HAVING count(username) > 1 ) 你如果只要username = 'arut'的话，把username IN （...） 换成username = 'arut' 即可

[qlkj666]

引用 4 楼 tu1425407707 的回复:

删除表中多余的重复记录，重复记录是根据单个字段（username）来判断，只留有userid最小的记录: DELETE FROM member WHERE username IN ( SELECT username FROM member GROUP BY username HAVING count(username) > 1 ) AND userid NOT IN ( SELECT min(userid) FROM member GROUP BY username HAVING count(username) > 1 ) 你如果只要username = 'arut'的话，把username IN （...） 换成username = 'arut' 即可

DELETE FROM member WHERE username IN ( username='arut' ) AND userid NOT IN ( SELECT min(userid) FROM member GROUP BY username HAVING count(username) > 1 ) 这样吗？

[qlkj666]

高手在哪里？

[徐卡丘]

DELETE FROM member WHERE username = 'arut' AND userid NOT IN ( SELECT min(userid) FROM member GROUP BY username HAVING count(username) > 1 )

[qlkj666]

引用 6 楼 tu1425407707 的回复:

DELETE FROM member WHERE username = 'arut' AND userid NOT IN ( SELECT min(userid) FROM member GROUP BY username HAVING count(username) > 1 )

测试了一下，无效，删除不了： mysql_query("DELETE FROM member WHERE openid='om-OA0-eLB0RIBFP27hS6YxDxQHg' AND id NOT IN (SELECT min(id) FROM member GROUP BY openid HAVING count(openid) > 1)");

[徐卡丘]

引用 7 楼 qlkj666 的回复:

[quote=引用 6 楼 tu1425407707 的回复:]
DELETE FROM member
WHERE username = 'arut'
AND userid NOT IN (
SELECT min(userid) FROM member
GROUP BY username
HAVING
count(username) > 1
)

测试了一下，无效，删除不了：

mysql_query("DELETE FROM member WHERE openid='om-OA0-eLB0RIBFP27hS6YxDxQHg' AND id NOT IN (SELECT min(id) FROM member GROUP BY openid HAVING count(openid) > 1)");[/quote]

修改后：
DELETE FROM test
WHERE username = 'tt'
AND id NOT IN (
select * from
(
SELECT min(id) FROM test
GROUP BY username
HAVING
count(username) > 1
)as a
);
删除前：

删除后：

[徐卡丘]

这回我是亲测一下的，再不对就没天理了

[徐卡丘]

我现在删除的是“re”,是对的呀，尴了个尬

[qlkj666]

引用 9 楼 tu1425407707 的回复:

这回我是亲测一下的，再不对就没天理了

真的居然还不对，这样是的所有符合条件的都删除了，并没有保留ID最小的那条： mysql_query("DELETE FROM member WHERE openid = 'om-OA047C5ZA0wNzVvG_haHcIjXM' AND id NOT IN (select * from (SELECT min(id) FROM member GROUP BY openid HAVING count(openid) > 1)as a)"); 你参杂一些其它数据在里面再测试下，因为还有username 不等于 'tt'的数据，所以ID也有其它的，不一定是的1开始的，但要保留username = 'tt'最小ID的那条数据