SQL Server數據庫刪除數據集中反復數據實例講授

SQL Server數據庫刪除數據集中反復數據實例講授。本站提示廣大學習愛好者：（SQL Server數據庫刪除數據集中反復數據實例講授）文章只能為提供參考，不一定能成為您想要的結果。以下是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數據庫刪除數據集中反復數據的進程，願望本次的引見可以或許對您有所收成！