SQL删除重复数据的五种方式

数据量2000，其中有1000重复

 

 

 

 -------------------------------------------------------------------------------------

 

 --方法一,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,

            @AreaOrganID 

    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 行受影响)

 */