查看: 1583|回复: 0

[Mysql数据库] mysql——查询重复数据,及删除重复数据只保留一条数据

发表于 2018-1-9 08:00:00

查询 text 表中,user_name字段值重复的数据及重复次数

  1. select user_name,count(*) as count from text
  2. group by user_name having count>1
复制代码

删除 text 表中,重复出现的数据只保留 ID 最小的一条数据,没有重复的数据不删除。

  1. delete from text where DW_DATE=20171227 AND
  2. id not in( select id from (select max(id) as id,count(user_name) as count from text
  3. WHERE DW_DATE=20171227
  4. group by user_name having count =1 order by count desc) as tab)
  5. AND
  6. id not in( select id from (select max(id) as id,count(user_name) as count from text
  7. WHERE DW_DATE=20171227
  8. group by user_name having count >1 order by count desc) as tab)
复制代码



回复

使用道具 举报