【MSSQL】删除表中重复数据
** 删除思路：**> 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
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
from Sys_UserLogOn_Tmp;
END
-- 删除新表
DROP TABLE Sys_UserLogOn_Tmp;
页:
[1]