MS SQLSERVER删除重复数据且只保留一条

背景：

表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;