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),1,16),checktype having count(1)>1 )