mysql删除表元素_Mysql的一些操作（删除表中的重复元素，查询重复元素）

问题1：去除person表中的重复元素(如下图所示)

正确答案如下代码一：

delete from person where

pname in (select a.pn from

(select pname pn from person group by pname having count(pname) > 1)a

)

and

pid not in (select b.pi from

(select min(pid) pi from person group by pname having count(pname) > 1)b

)

结果如下：

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

一般有人会这样写，但会出错，如下图1-1所示

delete from person where

pname in (select pname pn from person group by pname having count(pname) > 1)

and

pid not in (select min(pid) pi from person group by pname having count(pname) > 1)

图1-1 失败代码

这样写会报错：[Err] 1093 - You can't specify target table 'person' for update in FROM clause(意思是不能先select出同一表中的某些值，再update这个表(在同一语句中)。)

所以如代码一所示，将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql里面，SqlServer和oracle不会出现此问题。

代码二：多字段删除重复的

如下表中数据：

执行代码如下：

delete from person where

(pname,pvalue) in (select a.pn,a.pv from

(select pname pn,pvalue pv from person group by pname,pvalue having count(*) > 1)a

)

and

pid not in (select b.pi from

(select min(pid) pi from person group by pname,pvalue having count(*) > 1)b

)结果图：

问题二：查询表中的重复元素

select pname from person group by pname having count(pname) > 1

显示所有的重复元素：

select * from person where pname in (select pname from person group by pname having count(pname) > 1)