sqlserver 2008 R2 删除重复数据

概述推荐方法3   --方法1： SELECT  * FROM    ( SELECT    Row_Number() OVER ( PARTITION BY [orderno] ORDER BY ( SELECT                                                               0                               

推荐方法3

 

--方法1：
SELECT  *
FROM    ( SELECT    Row_Number() OVER ( PARTITION BY [orderno] ORDER BY ( SELECT
                                                              0
                                                              ) ) AS RowNO,
                    *
          FROM      tblMulCharge
        ) t
WHERE   T.RowNO > 1 ;

 

--方法2：

WITH    ct01
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY orderno ORDER BY ( SELECT
                                                              0
                                                              ) ) AS rn
               FROM     tblMulCharge
             )
    DELETE  FROM ct01
    WHERE   rn > 1
    go

--方法3:针对大数据

WITH    ct01           AS ( SELECT   [ID],                        [OriOrderNo],                        [OrderNo],                        [TotalAmount],                        [PayAmount],                        [ProvIDerAmount],                        [transaction_ID],                        [PNRCode],                        [Consumer],                        [ProvIDer],                        [SellerAccount],                        [BuyerAccount],                        [State],                        [PayTime],                        [PayInfo],                        [refundTime],                        [refund_ID],                        [refund_info],                        [refundTimeV],                        [refund_ID_V],                        [refund_info_V],                        [refundAmount],                        ROW_NUMBER() OVER ( PARTITION BY orderno ORDER BY ( SELECT                                                               0                                                               ) ) AS rn                FROM     tblMulCharge              )     SELECT      [ID]      ,[OriOrderNo]      ,[OrderNo]      ,[TotalAmount]      ,[PayAmount]      ,[ProvIDerAmount]      ,[transaction_ID]      ,[PNRCode]      ,[Consumer]      ,[ProvIDer]      ,[SellerAccount]      ,[BuyerAccount]      ,[State]      ,[PayTime]      ,[PayInfo]      ,[refundTime]      ,[refund_ID]      ,[refund_info]      ,[refundTimeV]      ,[refund_ID_V]      ,[refund_info_V]      ,[refundAmount]     INTO    dbo.tblMulCharge_tmp     FROM    ct01     WHERE   rn = 1 DROP table dbo.tblMulCharge ;   EXEC sp_rename 'dbo.tblMulCharge_tmp','tblMulCharge'

总结

以上是内存溢出为你收集整理的sqlserver 2008 R2 删除重复数据全部内容，希望文章能够帮你解决sqlserver 2008 R2 删除重复数据所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。