如何删除資料集中重複的資料

有時對于表中的結果集，滿足一定規則我們則認為是重複資料，而這些重複資料需要删除。如何删除呢？見下面的例子（裡面有一個用法隻有在sql server 2005以上才有這個寫法）

如下隻要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