利用SQL05特性删除表中重复数据

问题:一个表有自增的ID列，表中有一些记录内容重复，也就是说这些记录除了ID不同之外，其他的信息都相同。需要把重复的记录保留一条，剩下的删除。

这种需求一般开发人员都会，我这里写出两个版本。

版本一：由于记录有自增列，所以自增列可以做为记录的唯一标识，由此可见，重复的记录的自增ID是一个递增关系，这里我们可以只保留ID最小的那条记录，其它的全部删除。利用一个嵌套语句就非常容易写出下面的SQL。其中的sname,saddress是记录除了ID外的所有列。

DELETE　　FROM　a
WHERE　　　id　NOT　IN　(　SELECT　　MIN(id)
　　　　　　　　　　　　　　　　　　　　FROM　　　　a
　　　　　　　　　　　　　　　　　　　　GROUP　BY　sname,
　　　　　　　　　　　　　　　　　　　　　　　　　　　　saddress　)

版本二：充分利用SQL05的几个比较实用的特性。这里先简单说说要用到的几个特性。详细用法可到网上搜索下。

1：ROW_NUMBER，它的作用就是用来生成行号，默认是从1开始。

2：公用表表达式（CTE），我这里并不会利用它的递归，而是用它来简化嵌套查询及对表自身引用功能。CTE的语法如下：

[　WITH　<common_table_expression>　[　,n　]　]
<common_table_expression>::=
　　　　　　　　expression_name　[　(　column_name　[　,n　]　)　]
　　　　AS
　　　　　　　　(　CTE_query_definition　)

说明：1>CTE在某种程序上相当表变量或者临时表的功能。但比起表变量来说它最大的优势是对自身的引用，CTE语句后面紧跟的select ,update,delete等，操作的结果都会直接反应的实际物理表中。相比临时表，最大优势无非是性能，临时表实际是一张物理存在的表，在对它进行操作时，会产生额外的IO开销以及管理上的开销。

2>CTE语法后面需要直接跟上使用CTE的相关语句select ,update,delete等,否则CTE会失效,下面的语句是错误的：

代码

WITH　　　b　AS　(　SELECT　　　ROW_NUMBER()　OVER　(　PARTITION　BY　sname,　saddress　ORDER　BY　sname,　saddress　)　AS　rn,
　　　　　　　　　　　　　　　　　　　　　　　　*
　　　　　　　　　　　　　　　FROM　　　　　a
　　　　　　　　　　　　　)
　　　　DELETE　　FROM　b
　　　　WHERE　　　rn　>　1
　SELECT　*　from　a
　SELECT　*　FROM　b　WHERE　rn>1

3：PARTITION BY，分区函数。和聚合函数不同的地方在于它能返回一个分组中的多条记录，聚合函数一般只有一条反映统计值的记录，partition　 by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组 。

经过上面的三个关键字的介绍后，下面给出三者相结合后的结果。

代码

WITH　　　b　AS　(　SELECT　　　ROW_NUMBER()　OVER　(　PARTITION　BY　sname,　saddress　ORDER　BY　sname,　saddress　)　AS　rn,
　　　　　　　　　　　　　　　　　　　　　　　　*
　　　　　　　　　　　　　　　FROM　　　　　a
　　　　　　　　　　　　　)
　　　　DELETE　　FROM　b
　　　　WHERE　　　rn　>　1

版本一和版本二比较：

1：版本二更加容易阅读。

2：版本二性能较版本一强。我们可以通过以以信息来看。可以看到版本一会发生两次表扫描。

代码

Table　'a'.　Scan　count　2,　logical　reads　4,　physical　reads　0,　read-ahead　reads　0,　lob　logical　reads　0,　lob　physical　reads　0,　lob　read-ahead　reads　0.
Table　'Worktable'.　Scan　count　1,　logical　reads　0,　physical　reads　0,　read-ahead　reads　0,　lob　logical　reads　0,　lob　physical　reads　0,　lob　read-ahead　reads　0.
(0　row(s)　affected)
Table　'a'.　Scan　count　1,　logical　reads　2,　physical　reads　0,　read-ahead　reads　0,　lob　logical　reads　0,　lob　physical　reads　0,　lob　read-ahead　reads　0.
(0　row(s)　affected)