ORACLE查询重复数据

系统管理员 2023-10-07 22:53 40阅读 0赞

1、查找表中多余的重复记录,重复记录是根据单个字段(userCode)来判断

  1. select
  2. *
  3. from
  4. user
  5. where
  6. userCode
  7. in
  8. (select userCode from user group by userCode having count (userCode) > 1)

2、删除表中多余的重复记录,重复记录是根据单个字段(userCode)来判断,只留有rowid最小的记录

  1. delete from
  2. user
  3. where
  4. userCode
  5. in
  6. (select userCode from user group by userCode having count (peopleId) > 1)
  7. and rowid not in
  8. (select min(rowid) from user group by userCode having count(userCode)>1)

3、查找表中多余的重复记录(多个字段)

  1. select
  2. *
  3. from
  4. user a
  5. where
  6. (a.userCode,a.userName)
  7. in
  8. (select userCode,userName from user group by userCode,userName having count(*) > 1)

4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录

  1. delete from
  2. user a
  3. where
  4. (a.userCode,a.userName)
  5. in
  6. (select userCode,userName from user group by userCode,userName having count(*) > 1)
  7. and rowid not in
  8. (select min(rowid) from user group by userCode,userName having count(*)>1)

5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录

  1. select
  2. *
  3. from
  4. user a
  5. where
  6. (a.userCode,a.userName)
  7. in
  8. (select userCode,userName from user group by userCode,userName having count(*) > 1)
  9. and rowid not in
  10. (select min(rowid) from user group by userCode,userName having count(*)>1)

发表评论

表情:
评论列表 (有 0 条评论,40人围观)

还没有评论,来说两句吧...

相关阅读