SQLServer 数据去重的一种处理方法（增加一个自增字段,利用自增

delete  FROM CHECKINOUT 
WHERE (CAST(userid AS varchar) +'-'+CAST(CHECKTIME AS varchar)) IN
          (SELECT CAST(userid AS varchar) +'-'+CAST(CHECKTIME AS varchar)
         FROM CHECKINOUT
         GROUP BY CAST(userid AS varchar) +'-'+CAST(CHECKTIME AS varchar)
         HAVING COUNT(*) > 1)
 AND rowid NOT IN
          (SELECT MIN(rowid)
         FROM CHECKINOUT
         GROUP BY userid,checktime
         HAVING COUNT(*) > 1)




--SELECT identity(int,1,1) as rowid,CHECKINOUT_2.* into CHECKINOUT from CHECKINOUT_2  --关键地方 （通过重新创建表，增加自增字段，来执行下面的语句，做到删重数据）

delete from ?checkinout where rowid in (
select max(rowid) from checkinout ?/*where userid=2 and checktime>'2015-06-21'*/ ?group by userid,substring(convert(varchar,checktime,120),16),checktype having count(1)>1 )

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!