sqlserver 2008 R2 删除重复数据

推荐方法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'

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!