SQL Server数据库删除数据集中重复数据实例讲解
16lz
2022-01-08
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
更多相关文章
- 《Android和PHP最佳实践》官方站
- mybatisplus的坑 insert标签insert into select无参数问题的解决
- python起点网月票榜字体反爬案例
- 《Android开发从零开始》——25.数据存储(4)
- Android系统配置数据库注释(settings.db)
- android用户界面之按钮(Button)教程实例汇
- Android中不同应用间实现SharedPreferences数据共享
- TabHost与RadioGroup结合完成的菜单【带效果图】5个Activity
- android图表ichartjs