MySQL和Oracle关于重复数据的操作（查找、删除）

假设有employee这张表，其中emp_name可能有重复，emp_id是主键(没有主键的写法在最后)

以下是MySQL和Oracle中关于重复数据的删除查找操作

查询重复数据：

Oracle、MySQL

select * from employee group by emp_name having count (*)>1;

查询可以删除的重复数据：

Oracle：

select t1.* from employee t1 where 
t1.emp_name in 
(SELECT t2.emp_name from employee t2 group by emp_name having count (*)>1) 
and 
t1.emp_id not in
(select min(t3.emp_id) from employee t3 group by emp_name having count (*)>1);

MySQL：

select t1.* from employee t1 where 
t1.emp_name in 
(select t4.emp_name from (select t2.emp_name from employee t2 group by t2.emp_name having count(*)>1) t4) 
and
t1.emp_id not in 
(select t5.emp_id from (select min(t3.emp_id) as emp_id from employee t3 group by t3.emp_name having count(*)>1) t5);

删除重复数据：

Oracle：

delete t1 from employee t1 where 
t1.emp_name in 
(SELECT t2.emp_name from employee t2 group by emp_name having count (*)>1) 
and
t1.emp_id not in 
(select min(t3.emp_id) from employee t3 group by emp_name having count (*)>1);

MySQL：

delete t1 from employee t1 where 
t1.emp_name in 
(select t4.emp_name from (select t2.emp_name from employee t2 group by t2.emp_name having count(*)>1) t4) 
and 
t1.emp_id not in 
(select t5.emp_id from (select min(t3.emp_id) as emp_id from employee t3 group by t3.emp_name having count(*)>1) t5);

没有主键的情况下删除重复记录是面试时经常会问到的，此时Oracle和MySQL的处理方法也有所不同。

Oracle：

主要利用了rowid，rowid它是Oracle的一个伪列，它并不实际存在于表中。它是ORACLE在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。我们可以把它当成一条记录的唯一标识来使用。只保留一个rowid最大或者最小的记录即可。如果主键是number类型，也可以使用这种方法，把rowid换成主键名即可。

delete from employee t1 where
t1.rowid > 
(select min(t2.rowid) from employee t2 where t1.emp_name=t2.emp_name);

MySQL：

MySQL没有类似Oracle的rowid，所以操作会比较麻烦。

1.查询重复记录，将查询的数据插入一个新的表中
2.删除原来的表的重复数据
3.将新表的数据再插入原表中
4.删除新表

create table new_table
(select * from employee group by emp_name having count(*)>1);

delete employee.* from employee where emp_name in
(select t.* from
(select emp_name from employee group by emp_name having count(*)>1) t);

第二步这里查出的重复数据需要嵌套一层查询。如果先查出同一表中的某些值，再修改这个表的内容(在同一语句中)会出现 You can't specify target table 的错误。

insert into employee (select * from new_table);

drop table new_table;