SQL删除重复数据只保留一条

一、查询全部的重复数据

  select * from CHECKINOUT  where userid  in (select USERID from CHECKINOUT GROUP by USERID,checktime HAVING count(*)>1
) and checktime    in (select checktime from CHECKINOUT group by USERID,checktime having count(*) > 1)

二、删除重复数据保留一条记录

 1、首先在表中新增一列自动增长列

      Alter Table CHECKINOUT  Add ID int Identity(1,1)

 2、在删除重复数据保留一条记录

  Delete from CHECKINOUT  Where ID Not In (
  Select MIN(ID) From CHECKINOUT  Group by USERID,CHECKTIME )

3、最后再在表中删除新建的自动增长列

 

4、

select PropertyNo from pro_propertys
where isdelete=0 and propertyno in
(
select propertyno From pro_propertys
where isdelete=0
group by propertyno
having count(1)>1
)

转载于:https://www.cnblogs.com/starts/p/5258830.html