SQL Server数据库删除数据集中重复数据实例讲解

数据库  /  管理员 发布于 6年前   137

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数据库删除数据集中重复数据的过程，希望本次的介绍能够对您有所收获！