SQL Server数据库删除数据集中重复数据实例讲解
程序员文章站
2024-01-12 08:11:34
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数据库删除数据集中重复数据的过程,希望本次的介绍能够对您有所收获!
下一篇: 1060:均值