Recommended Method 3
--Method 1:
SELECT *
From (select Row_number ()-PARTITION by [OrderNo] ORDER by (select
0
) as Rowno,
*
From Tblmulcharge
) T
WHERE t.rowno > 1;
--Method 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
--Method 3: For large data
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 '
See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/