MSSQLSERVER数据库- SQL删除重复数据的五种方式
删除重复的数据，在平时的工作中还是会和碰到的，感觉挺有用，从网上摘录的，记在这里，以备需要时查阅
--方法一,IN方式，适合2000/2005/2008,6728 毫秒
DELETE [student_L]
WHERE id NOT IN ( SELECT MAX(id)--min(id)
FROM [student_L]
GROUP BY [stuid], [stuname], [Birthday], [AreaOrganID] )
/*
SQL Server 分析和编译时间:
CPU 时间= 20 毫秒，占用时间= 20 毫秒。
表'student_L'。扫描计数2001，逻辑读取30014 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'Worktable'。扫描计数1，逻辑读取2020 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
SQL Server 执行时间:
CPU 时间= 6719 毫秒，占用时间= 6728 毫秒。
(1000 行受影响)
*/
--方法二,CTE方式，适合2005/2008,30 毫秒
WITH dstudent
AS (
SELECT id,
ROW_NUMBER() OVER ( PARTITION BY [stuid], [stuname],
[Birthday], [AreaOrganID] ORDER BY GETDATE() ) pid
FROM [student_L]
)
DELETE dstudent
WHERE id NOT IN ( SELECT id
FROM dstudent
WHERE pid = 1 )
/*
SQL Server 分析和编译时间:
CPU 时间= 12 毫秒，占用时间= 12 毫秒。
表'student_L'。扫描计数4，逻辑读取2056 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'Worktable'。扫描计数1，逻辑读取2020 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
SQL Server 执行时间:
CPU 时间= 32 毫秒，占用时间= 30 毫秒。
(1000 行受影响)
*/
--方法三, LEFT JOIN方式,适合2000/2005/2008,7379 毫秒
DELETE stu
FROM [student_L] stu
LEFT JOIN (
SELECT ID = MAX(id)
FROM [student_L]
GROUP BY [stuid], [stuname], [Birthday], [AreaOrganID]
) S ON stu.id = s.id
WHERE s.id IS NULL
/*
SQL Server 分析和编译时间:
CPU 时间= 10 毫秒，占用时间= 10 毫秒。
表'student_L'。扫描计数2001，逻辑读取30014 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'Worktable'。扫描计数0，逻辑读取0 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
SQL Server 执行时间:
CPU 时间= 7375 毫秒，占用时间= 7379 毫秒。
(1000 行受影响)
*/
--方法四,游标方式, 适合2000/2005/2008,833毫秒
DECLARE @d DATETIME
SET @d = GETDATE()
--Script.Start
---------------------------------------------------------------------------------------------
DECLARE @id INT
DECLARE @stuid INT
DECLARE @stuname VARCHAR(50)
DECLARE @Birthday VARCHAR(10)
DECLARE @AreaOrganID INT
DECLARE @prestuid INT
DECLARE @prestuname VARCHAR(50)
DECLARE @preBirthday VARCHAR(10)
DECLARE @preAreaOrganID INT
DECLARE cur CURSOR
FOR
SELECT ID, [stuid], [stuname], [Birthday], [AreaOrganID]
FROM [student_L]
ORDER BY [stuid], [stuname], [Birthday], [AreaOrganID]
OPEN cur
FETCH NEXT FROM cur INTO @id, @stuid, @stuname, @Birthday, @AreaOrganID
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( @stuid = @prestuid )
AND ( @stuname = @prestuname )
AND ( @Birthday = @preBirthday )
AND ( @AreaOrganID = preAreaOrganID )
BEGIN
DELETE [student_L]
WHERE id = @id
END
SELECT @prestuid = @stuid, @prestuname = @stuname,
@preBirthday = @Birthday, @preAreaOrganID = @AreaOrganID
FETCH NEXT FROM cur INTO @id, @stuid, @stuname, @Birthday,
@ArerOrganID
END
CLOSE cur
DEALLOCATE cur
---------------------------------------------------------------------------------------------
--Script.End
PRINT DATEDIFF(ms, @d, GETDATE())
--方法五,中间表方式, 适合/2005/2008 ,39 毫秒。
SELECT (
SELECT TOP 1
id
FROM student_l a
WHERE 1 = 1
AND a.stuid = b.stuid
AND a.[stuname] = b.[stuname]
AND a.[Birthday] = b.[Birthday]
AND a.[AreaOrganID] = b.[AreaOrganID]
) id, [stuid], [stuname], [Birthday], [AreaOrganID]
INTO student_l1
FROM student_l b
DROP TABLE student_l
EXEC sp_rename 'dbo.student_l1', 'student_l';
/*
SQL Server 分析和编译时间:
CPU 时间= 0 毫秒，占用时间= 3 毫秒。
表'Worktable'。扫描计数2000，逻辑读取8067 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。
表'student_l'。扫描计数2，逻辑读取22 次，物理读取0 次，预读0 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次
SQL Server 执行时间:
CPU 时间= 31 毫秒，占用时间= 39 毫秒。
(2000 行受影响)
*/
