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 )

 

转：https://www.cnblogs.com/wangfuyou/p/6058169.html