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數據庫刪除數據集中重復數據的過程,希望本次的介紹能夠對您有所收獲!