在数据库中经常会遇到大量的重复数据，如果这些脏数据需要清除，那下面的内容就非常是为你量身定做的哦，若不是，请忽略！

假设有个dept数据表，内容如下：

deptno	dname
1	开发部
2	财务部
3	市场部
4	后勤部
5	公关部
9	测试部
10	开发部
11	测试部
12	后勤部

• 查找重复项

select dname from dept group by dname having count(dname) > 1;

• 筛选出保留项，即不能删除的

select min(deptno) -- max(deptno)
from dept
group by dname
having count(dname) > 1;

• 删除重复项

delete from dept where
dname in (select dname from dept group by dname having count(dname) > 1)
and deptno not in (select min(deptno) as detpno from dept group by dname having count(dname) > 1);

注: 上述sql脚本，可运行于 MS SQL , Oracle

• MySql下删除重复项

delete from dept where
dname in (select * from(select dname from dept group by dname having count(dname) > 1) a)
and deptno not in (select * from (select min(deptno) as deptno from dept group by dname having count(dname) > 1) b);