SQL Server數(shù)據(jù)庫(kù)操作中，有時(shí)對(duì)于表中的結(jié)果集，滿足一定規(guī)則我們則認(rèn)為是重復(fù)數(shù)據(jù)，而這些重復(fù)數(shù)據(jù)需要?jiǎng)h除。如何刪除呢？本文我們通過(guò)一個(gè)例子來(lái)加以說(shuō)明。
如下只要companyName,invoiceNumber,customerNumber三者都相同，我們則認(rèn)為是重復(fù)數(shù)據(jù)，下面的例子演示了如何刪除。
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數(shù)據(jù)庫(kù)刪除數(shù)據(jù)集中重復(fù)數(shù)據(jù)的過(guò)程，希望本次的介紹能夠?qū)δ兴斋@！
