In SQL Server database operation, sometimes for the result set in the table, if it meets certain rules, we think it is duplicate data, and these duplicate data need to be deleted. How to delete it? In this paper, we use an example to illustrate.
Examples are as follows:
As long as the CompanyName, invoicenumber and customernumber are the same, we think they are duplicate data. The following example shows how to delete them.
declare @InvoiceListMaster table ( ID int identity primary key ,
companyName Nchar(20),
invoiceNumber int,
CustomerNumber int,
rmaNumber int )
insert @InvoiceListMaster
Select n 'Huawei', 1001100200
union all
Select n 'Huawei', 1001100300
union all
Select n 'Huawei', 1001100301
union all
Select n 'ZTE', 1002, 200, 1
union all
Select n 'ZTE', 1002, 200, 2
select * from @InvoiceListMaster
DELETE A
from (
select rown = ROW_NUMBER( )over( partition by companyname,
invoicenumber,
customerNumber
order by companyname,
invoicenumber,
customerNumber ),
companyname,
invoicenumber,
customerNumber
from @InvoiceListMaster )a
where exists ( select 1
from ( select rown = ROW_NUMBER( )over( partition by companyname,
invoicenumber,
customerNumber
order by companyname,
invoicenumber,
customerNumber ),
companyname,
invoicenumber,
customerNumber
from @InvoiceListMaster ) b
where b.companyName = a.companyName
and b.invoiceNumber = a.invoiceNumber
and b.CustomerNumber = a.CustomerNumber
and a.rown > b.rown
)
select * from @InvoiceListMasterThe above example demonstrates the process of deleting duplicate data in the data set of SQL Server database. I hope this introduction can be helpful to you!