背景:
表Per_CheckIn,主要字段:pid--身份证号,checktime-刷身份证时间,由于误操作,导致了重复记录产生,现需将pid和checktime一样的记录删除。
具体表结构:
CREATE TABLE [Per_CheckIn]( [id] [INT] IDENTITY(1,1) NOT NULL, [pid] [CHAR](18) NULL, [pname] [NVARCHAR](32) NULL, [nation] [NVARCHAR](30) NULL, [addr] [NVARCHAR](150) NULL, [checktime] [DATETIME] NULL, [uptime] [DATETIME] NULL, [Birthday] [DATETIME] NULL, [StartDate] [DATETIME] NULL, [EndDate] [DATETIME] NULL, [Organ] [NVARCHAR](500) NULL, [Photo] [NTEXT] NULL )
思路:
1.将重复记录导入临时表#tmp:
SELECT IDENTITY( INT,1,1 ) AS id, [pid], [pname], [nation], [addr], [checktime], [uptime], [Birthday], [StartDate], [EndDate], [Organ], [Photo] INTO #tmp FROM [Per_CheckIn] WHERE pid IN ( SELECT pid FROM [Per_CheckIn] GROUP BY pid, checktime HAVING COUNT(*) > 1 ) ORDER BY pid,checktime;
2.删除[Per_CheckIn]表中重复的记录:
DELETE FROM [Per_CheckIn] WHERE pid IN ( SELECT pid FROM [Per_CheckIn] GROUP BY pid,checktime HAVING COUNT(*) > 1 );
3.给重复数据一个自增ID,过滤出每组里面最小ID,将数据再将最小ID插入:
INSERT INTO [Per_CheckIn] SELECT [pid], [pname], [nation], [addr], [checktime], [uptime], [Birthday], [StartDate], [EndDate], [Organ], [Photo] FROM #tmp a WHERE id IN ( SELECT MIN(id) FROM #tmp b WHERE a.pid = b.pid AND a.checktime = b.checktime GROUP BY pid,checktime );
4.删除临时表,完成操作:
DROP TABLE #tmp;