sql 查出一张表中重复的所有记录数据

最新推荐文章于 2025-01-11 10:26:31 发布

weixin_33860553

最新推荐文章于 2025-01-11 10:26:31 发布

阅读量129

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/youguess/p/10490532.html

本文详细介绍如何使用SQL查询去除数据表中的重复记录，包括查询所有重复数据、仅展示ID最小的重复记录以及删除除ID最小外的所有重复记录的方法。通过具体SQL语句示例，为数据库管理员和开发者提供实用的去重解决方案。

1.查出所有重复的数据(多列)，根据 datetime,staff_num,state

select * from gs_collect_staff a where (a.datetime,a.staff_num,a.state)
 in (select datetime,staff_num,state from gs_collect_staff group by staff_num,datetime,state
  having count(*) > 1 )  order by datetime desc

2.查收所有重复的数据(多列)，只显示id最小的那个值

select * from gs_collect_staff a where (a.datetime,a.staff_num,a.state)
 in (select datetime,staff_num,state from gs_collect_staff group by staff_num,datetime,state
  having count(*) > 1 )  
  and a.id  in(select min(id)from gs_collect_staff group by staff_num,datetime,state
  having count(*) > 1 )
   order by datetime desc

3.删除重复的数据(多列)，只保留id最小的那个值

 delete from gs_collect_staff a where (a.datetime,a.staff_num,a.state)
 in (select datetime,staff_num,state from gs_collect_staff group by staff_num,datetime,state
  having count(*) > 1 )  --order by datetime desc
  and a.id not in(select min(id)from gs_collect_staff group by staff_num,datetime,state
  having count(*) > 1 )