删除表中重复数据sql语句

  1. 查询表中重复数据
    select * from employee where employeeId in (select employeeId from employee group by employeeId having count(employeeId) > 1)

  2. 删除表中多余的重复记录,重复记录是根据单个字段(employeeId)来判断,只留有rowid最小的记录
    delete from employee where employeeId in (select employeeId from employee group by employeeId having count(employeeId) > 1)
    and rowid not in (select min(rowid) from employee group by employeeId having count(employeeId )>1)

  3. 查找表中多余的重复记录(多个字段)
    select * from employee e
    where (e.employeeId,e.phoneNo) in (select employeeId,phoneNo from employee group by employeeId,phoneNo having count(*) > 1)

  4. 删除表中多余的重复记录(多个字段),只留有rowid最小的记录
    delete from employee e
    where (e.employeeId, e.phoneNo) in (select employeeId, phoneNo from employee group by employeeId,phoneNo having count(*) > 1)
    and rowid not in (select min(rowid) from employee group by employeeId,phoneNo having count(*)>1)

  5. 查找表中多余的重复记录(多个字段),不包含rowid最小的记录
    select * from employee e
    where (e.employeeId,e.phoneNo) in (select employeeId, phoneNo from employee group by employeeId,phoneNo having count(*) > 1)
    and rowid not in (select min(rowid) from employee group by employeeId,phoneNo having count(*)>1)


通用:

delete from table t
where (t.字段1, t.字段2, … , t.字段n) in (select 字段1, 字段2, … , 字段n from table group by 字段1, 字段2, … , 字段n having count(*) > 1)
and rowid not in (select min(rowid) from table group by 字段1, 字段2, … , 字段n having count(*)>1)

此外:

如果只是查询的时候, 不显示重复, 只需select distinct 字段 from table….

阅读更多
个人分类: SQL
上一篇Java中遍历Map的五种方法
下一篇PL/SQL1 基本查询与排序
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭