mysql、oracle 去重(数据库删除重复数据保留一条)

现在在数据库中有2对重复数据，2条正常数据，我想要把重复数据都删掉

讲一下思路

根据重复字段分组，并增加约束条件，查询出所有的，重复数据的最小id,和重复的属性

SELECT uname,MIN(uid) FROM tbl_user GROUP BY uname HAVING COUNT(uid)>1;

 

现在查询出来的id就是我们出现重复数据的字段，需要保留的那个字段的id

然后根据条件删除

重复的属性名字，但是却不是我想要保留的id都要删除掉

DELETE 
FROM
	tbl_user 
WHERE
	uname IN ( SELECT uname FROM tbl_user GROUP BY uname HAVING COUNT( uid ) > 1 ) 
	AND uid NOT IN ( SELECT MIN( uid ) FROM tbl_user GROUP BY uname HAVING COUNT( uid ) > 1 )

注意如果是mysql数据库，会报错

1093 - You can't specify target table 'tbl_user' for update in FROM clause

原因：

mysql 数据库不可以在使用同一张表的查询为条件更新这张表

解决办法:将查询的数据起别名再次查询，包装一下。

DELETE 
FROM
	tbl_user 
WHERE
	uname IN 
( SELECT * FROM ( SELECT uname FROM tbl_user GROUP BY uname HAVING COUNT( uid ) > 1 ) a ) 
	AND uid NOT IN 
( SELECT * FROM ( SELECT MIN( uid ) FROM tbl_user GROUP BY uname HAVING COUNT( uid ) > 1 ) a )

完美