【MSSQL】删除表中重复数据

lanwd

删除思路：

1.将要删除的重复数据全部插入到带有自增ID的新表；
2.保留同Id中“ 自增Id最小”一条，同Id其他的都删除；
3.删除原表重复数据；
4.插入新表数据；
5.删除新表。

实现代码如下：

-- 将要删除的重复数据全部插入到带有自增ID的新表 Sys_UserLogOn_Tmp ；
select identity(int,1,1) as autoID, into Sys_UserLogOn_Tmp FROM Sys_UserLogOn
WHERE F_id IN (
SELECT F_Id FROM Sys_UserLogOn GROUP BY F_Id HAVING COUNT()>1
);
-- 保留同Id中“ 自增Id最小”一条，同Id其他的都删除；
DECLARE @autoID INT,@Id NVARCHAR(50);
DECLARE cur CURSOR FOR
SELECT F_Id FROM Sys_UserLogOn_Tmp ORDER BY F_Id
OPEN cur
FETCH NEXT FROM cur INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE Sys_UserLogOn_Tmp WHERE @id=F_Id AND autoID NOT IN(SELECT MIN(autoID) FROM Sys_UserLogOn_Tmp WHERE F_id=@id)  ;
FETCH NEXT FROM cur INTO @id
END
CLOSE cur ;
DEALLOCATE cur ;
-- 新表数据查看
select from Sys_UserLogOn_Tmp
-- 新表重复数据查看
SELECT F_Id,COUNT() FROM Sys_UserLogOn_Tmp group by F_Id having COUNT(*)>1
-- 删除原表数据
DELETE Sys_UserLogOn WHERE F_Id IN (SELECT F_Id FROM Sys_UserLogOn_Tmp)
--将新表数据插入到原表
INSERT INTO Sys_UserLogOn SELECT
[F_Id]
,[F_UserId]
,[F_UserPassword]
,[F_UserSecretkey]
,[F_AllowStartTime]
,[F_AllowEndTime]
,[F_LockStartDate]
,[F_LockEndDate]
,[F_FirstVisitTime]
,[F_PreviousVisitTime]
,[F_LastVisitTime]
,[F_ChangePasswordDate]
,[F_MultiUserLogin]
,[F_LogOnCount]
,[F_UserOnLine]
,[F_Question]
,[F_AnswerQuestion]
,[F_CheckIPAddress]
,[F_Language]
,[F_Theme]
,[F_CreatorTime]
,[F_CreatorUserId]
,[F_LastModifyTime]
,[F_LastModifyUserId]
,[F_DeleteMark]
,[F_DeleteTime]
,[F_DeleteUserId]
,[F_UpMark]
from Sys_UserLogOn_Tmp;
END
-- 删除新表
DROP TABLE Sys_UserLogOn_Tmp;