推薦方法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'
查看本欄目
