SQL Server2008中刪除重復記錄的方法分享_Mssql數據庫教程
      
編輯Tag賺U幣
      


      教程Tag：暫無Tag,歡迎添加,賺取U幣!
      


      

      推薦：使用Sqlserver事務發布實現數據同步(sql2008)
      事務的功能在sqlserver中由來已久，因為最近在做一個數據同步方案，所以有機會再次研究一下它以及快照等，發現還是有很多不錯的功能和改進的。這里以sqlserver2008的事務發布功能為例，對發布訂閱的方式簡要介紹一下操作流程，一方面做個總結備份，一方面與大家進行一下
      現在讓我們來看在SQL SERVER 2008中如何刪除這些記錄, 首先,可以模擬造一些簡單重復記錄:
      復制代碼 代碼如下:www.wf0088.com
       
      Create Table dbo.Employee ( 
      [Id] int Primary KEY , 
      [Name] varchar(50), 
      [Age] int, 
      [Sex] bit default 1 
      ) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(1,'James',25,default) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(2,'James',25,default) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(3,'James',25,default) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(4,'Lisa',24,0) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(5,'Lisa',24,0) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(6,'Lisa',24,0) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(7,'Mirsa',23,0) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(8,'Mirsa',23,0) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(9,'Mirsa',23,0) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(10,'John',26,default) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(11,'Abraham',28,default) 
      Insert Into Employee ([Id] , [Name] , [Age] , [Sex] ) Values(12,'Lincoln',30,default) 
       
      OK,首先我們使用最常見的方法: 

      Delete From Employee Where Name in (select NameFrom Employee Group By Name Having Count(Name)>1); 
      接著使用RowNumber(): 

      Delete T From( Select Row_Number() Over(Partition By [Name] Order By (SELECT 0)) As RowNumber,* From Employee) TWhere T.RowNumber > 1; 
      

      還可以使用
      復制代碼 代碼如下:www.wf0088.com
       
      With Dups as 
      ( 
      select ROW_NUMBER() Over(Partition by [Name] Order by (SELECT 0)) as rn 
      FROM Employee 
      ) 
      Delete From Dups 
      Where rn>1; 
       
      再加上RANK()的CTE: 
      復制代碼 代碼如下:www.wf0088.com
       
      WITH Dups As 
      ( 
      Select [ID],[Name],[Age],[Sex] 
      , ROW_NUMBER() OVER(Partition By [Name] Order By (SELECT 0)) AS rn 
      ,RANK() OVER(Partition By [Name] Order By (SELECT 0)) AS rnk 
      FROM Employee 
      ) 
      DELETE FROM Dups 
      WHERE rn<>rnk; 
       
      

      下面是這四個T-SQL查詢的執行計劃:
      

       
      

      你可以看到沒有用CTE的方法開銷最大, 主要是在Table Spool, 這里開銷了44%, Table Spool 是一個物理運算符。
      Table Spool 運算符掃描輸入，并將各行的一個副本放入隱藏的假脫機表中，此表存儲在 tempdb 數據庫中并且僅在查詢的生存期內存在。如果重繞該運算符（例如通過 Nested Loops 運算符重繞），但不需要任何重新綁定，則將使用假脫機數據，而不用重新掃描輸入。 
      注意上面的方法只是在重復記錄比較少的情況下, 如果重復記錄多. DELETE將會非常慢, 最好的方法是復制目標數據到另一個新表,刪除原來的表,重命名新表為原來的表. 或用臨時表, 這樣還可以減少數據庫事務日志. 看下面的T-SQL: 
      復制代碼 代碼如下:www.wf0088.com
       
      WITH Dups As 
      ( 
      Select [ID],[Name],[Age],[Sex] 
      , ROW_NUMBER() OVER(Partition By [ID] Order By (SELECT 0)) AS rn 
      FROM Employee 
      ) 
      Select [ID],[Name],[Age],[Sex] 
      INTO dbo.EmployeeDupsTmp 
      FROM Dups 
      WHERE rn=1 
      DROP TABLE dbo.Employee; 
      EXEC sp_rename 'dbo.EmployeeDupsTmp','Employee' 

      

      希望這篇POST對您開發有幫助.作者： 
      分享：sql2005創建遠程登錄帳戶的sql語句
      有時候我們需要創建遠程登錄賬號，這里簡單分享下，方便需要的朋友
      


      

      


      來源：模板無憂//所屬分類：Mssql數據庫教程/更新時間：2013-04-23


      


      



      


      



      相關Mssql數據庫教程：