SQL Server数据库操作中,有时对于表中的结果集,满足一定规则我们则认为是重复数据,而这些重复数据需要删除。如何删除呢?本文我们通过一个例子来加以说明。 例子如下: 如下只
SQL Server数据库操作中,有时对于表中的结果集,满足一定规则我们则认为是重复数据,而这些重复数据需要删除。如何删除呢?本文我们通过一个例子来加以说明。
例子如下:
如下只要companyName,invoiceNumber,customerNumber三者都相同,我们则认为是重复数据,下面的例子演示了如何删除。
declare @InvoiceListMaster table ( ID int identity primary key ,
companyName Nchar(20),
invoiceNumber int,
CustomerNumber int,
rmaNumber int )
insert @InvoiceListMaster
select N'华为', 1001,100,200
union all
select N'华为', 1001,100,300
union all
select N'华为', 1001,100,301
union all
select N'中兴', 1002, 200,1
union all
select N'中兴', 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 @InvoiceListMaster
以上的例子就演示了SQL Server数据库删除数据集中重复数据的过程,希望本次的介绍能够对您有所收获!