SQL Server数据库操作中，有时对于表中的结果集，满足一定规则我们则认为是重复数据，而这些重复数据需要删除。如何删除呢？本文我们通过一个例子来加以说明。
例子如下：
如下只要companyName,invoiceNumber,customerNumber三者都相同，我们则认为是重复数据，下面的例子演示了如何删除。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
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数据库删除数据集中重复数据的过程，希望本次的介绍能够对您有所收获！