[MS SQL]删除重复记录

删除重复记录
在具体的应用中，重复的数据可能是由于在插入数据的时候，没有做严格的数据过滤；另一种情况就是随着应用逻辑的变化，使得以前不是重复的记录，现在被认为是重复的数据了。针对这两种情况，我们分别来讨论。
1、 删除单一表中重复的记录
a. 当所有字段相同作为重复判别条件 这种情况由于完全不能区别两条相同数据的记录，这里利用了先删除后插入的方法。
代码:
[color=#0000bb]
[color=#007700]--创建表
[color=#007700]IF [color=#0000bb]EXISTS[color=#007700]([color=#0000bb]SELECT 1 FROM SYSOBJECTS WHERE name [color=#007700]= [color=#dd0000]';itpub'; [color=#007700]AND [color=#0000bb]type [color=#007700]= [color=#dd0000]';U';)
DROP TABLE itpub
GO
CREATE TABLE itpub[color=#007700]([color=#0000bb]col1 CHAR[color=#007700]([color=#0000bb]1[color=#007700]),[color=#0000bb]col2 CHAR[color=#007700]([color=#0000bb]2[color=#007700]),[color=#0000bb]col3 CHAR[color=#007700]([color=#0000bb]3))
GO
[color=#007700]--数据准备
INSERT INTO itpub SELECT [color=#dd0000]';A';[color=#007700],[color=#dd0000]';B';[color=#007700],[color=#dd0000]';C'; UNION ALL
                  SELECT [color=#dd0000]';D';[color=#007700],[color=#dd0000]';E';[color=#007700],[color=#dd0000]';F'; UNION ALL
                  SELECT [color=#dd0000]';G';[color=#007700],[color=#dd0000]';H';[color=#007700],[color=#dd0000]';I'; UNION ALL
                  SELECT [color=#dd0000]';D';[color=#007700],[color=#dd0000]';E';[color=#007700],[color=#dd0000]';F'; UNION ALL
                  SELECT [color=#dd0000]';G';[color=#007700],[color=#dd0000]';H';[color=#007700],[color=#dd0000]';I'; UNION ALL
                  SELECT [color=#dd0000]';D';[color=#007700],[color=#dd0000]';E';[color=#007700],';F';
[color=#007700]--从重复的记录中提取一条数据记录，存放在临时表中。
SELECT DISTINCT [color=#007700]* [color=#0000bb]INTO #temp FROM itpub
[color=#007700]--删除表itpub中的数据。
DELETE FROM itpub
[color=#007700]--将临时表中的数据插入到原表中
INSERT INTO itpub SELECT [color=#007700]* [color=#0000bb]FROM [color=#ff8000]#temp

很显然，当数据量非常大，而且又在表itpub中创建了索引，这种办法是非常不可取的，在性能上会有相当大的影响。
b. 当一部分字段相同作为重复判别的条件，既有条件的删除重复记录
表中存在主键 在表itpub中，重复记录的判别条件是当col1和col2重复。当存在主键时比较容易解决，这时可以做一个自连接处理，这种情况中，往往要求删除的数据都是有条件的，比如删除时间靠前的数据（这时符合条件的数据在时间字段上的取值肯定是不同的）等等。在例子中，我们删除的是id较小的重复数据，从而保存了重复数据中id最大的数据。
代码:
[color=#0000bb]
[color=#007700]--创建表
[color=#007700]IF [color=#0000bb]EXISTS[color=#007700]([color=#0000bb]SELECT 1 FROM SYSOBJECTS WHERE name [color=#007700]= [color=#dd0000]';itpub'; [color=#007700]AND [color=#0000bb]type [color=#007700]= [color=#dd0000]';U';)
DROP TABLE itpub
GO
CREATE TABLE itpub[color=#007700]([color=#0000bb]id INT PRIMARY KEY [color=#007700],[color=#0000bb]col1 CHAR[color=#007700]([color=#0000bb]1[color=#007700]),[color=#0000bb]col2 CHAR[color=#007700]([color=#0000bb]2[color=#007700]),[color=#0000bb]col3 CHAR[color=#007700]([color=#0000bb]3))
GO
[color=#007700]--数据准备
INSERT INTO itpub SELECT 1[color=#007700],[color=#dd0000]';A';[color=#007700],[color=#dd0000]';B';[color=#007700],[color=#dd0000]';C'; UNION ALL
                  SELECT 2[color=#007700],[color=#dd0000]';A';[color=#007700],[color=#dd0000]';B';[color=#007700],[color=#dd0000]';F'; UNION ALL
                  SELECT 3[color=#007700],[color=#dd0000]';G';[color=#007700],[color=#dd0000]';H';[color=#007700],[color=#dd0000]';I'; UNION ALL
                  SELECT 4[color=#007700],[color=#dd0000]';D';[color=#007700],[color=#dd0000]';E';[color=#007700],[color=#dd0000]';F'; UNION ALL
                  SELECT 5[color=#007700],[color=#dd0000]';G';[color=#007700],[color=#dd0000]';H';[color=#007700],[color=#dd0000]';I'; UNION ALL
                  SELECT 6[color=#007700],[color=#dd0000]';D';[color=#007700],[color=#dd0000]';E';[color=#007700],';F';
[color=#007700]--删除id偏小的重复记录
DELETE A FROM itpub A[color=#007700],([color=#0000bb]SELECT col1[color=#007700],[color=#0000bb]col2[color=#007700],[color=#0000bb]MAX[color=#007700]([color=#0000bb]id[color=#007700]) [color=#0000bb]id FROM itpub  GROUP BY col1[color=#007700],[color=#0000bb]col2 HAVING COUNT[color=#007700](*) > [color=#0000bb]1[color=#007700]) B
WHERE A[color=#007700].[color=#0000bb]id [color=#007700] [color=#0000bb]B[color=#007700].id
   [color=#007700]AND [color=#0000bb]A[color=#007700].[color=#0000bb]col1 [color=#007700]= [color=#0000bb]B[color=#007700].col1
   [color=#007700]AND [color=#0000bb]A[color=#007700].[color=#0000bb]col2 [color=#007700]= [color=#0000bb]B[color=#007700].
表中不存在主键 当表itpub中没有设置主键，重复记录的判断条件是col1和col2重复。这时需要借助临时表#temp极其identity函数构建主键。
代码:
[color=#0000bb]
[color=#007700]IF [color=#0000bb]EXISTS[color=#007700]([color=#0000bb]SELECT 1 FROM SYSOBJECTS WHERE name [color=#007700]= [color=#dd0000]';itpub'; [color=#007700]AND [color=#0000bb]type [color=#007700]= [color=#dd0000]';U';)
DROP TABLE itpub
GO
CREATE TABLE itpub[color=#007700]([color=#0000bb]col1 CHAR[color=#007700]([color=#0000bb]1[color=#007700]),[color=#0000bb]col2 CHAR[color=#007700]([color=#0000bb]2[color=#007700]),[color=#0000bb]col3 CHAR[color=#007700]([color=#0000bb]3))
GO
[color=#007700]--数据准备
INSERT INTO itpub SELECT [color=#dd0000]';A';[color=#007700],[color=#dd0000]';B';[color=#007700],[color=#dd0000]';C'; UNION ALL
                  SELECT [color=#dd0000]';A';[color=#007700],[color=#dd0000]';B';[color=#007700],[color=#dd0000]';F'; UNION ALL
                  SELECT [color=#dd0000]';G';[color=#007700],[color=#dd0000]';H';[color=#007700],[color=#dd0000]';I'; UNION ALL
                  SELECT [color=#dd0000]';D';[color=#007700],[color=#dd0000]';E';[color=#007700],[color=#dd0000]';F'; UNION ALL
                  SELECT [color=#dd0000]';G';[color=#007700],[color=#dd0000]';H';[color=#007700],[color=#dd0000]';I'; UNION ALL
                  SELECT [color=#dd0000]';D';[color=#007700],[color=#dd0000]';E';[color=#007700],';F';
[color=#007700]---[color=#0000bb]将数据拷贝到临时表#temp中，并增加标识列
[color=#0000bb]SELECT IDENTITY[color=#007700]([color=#0000bb]INT[color=#007700],[color=#0000bb]1[color=#007700],[color=#0000bb]1[color=#007700]) AS [color=#0000bb]ID[color=#007700],* [color=#0000bb]INTO #temp FROM itpub A
[color=#007700]--将itpub表中的数据删除
DELETE FROM itpub
[color=#007700]--------向itpub表中插入无重复的数据
INSERT INTO itpub SELECT A[color=#007700].[color=#0000bb]col1[color=#007700],[color=#0000bb]A[color=#007700].[color=#0000bb]col2[color=#007700],[color=#0000bb]A[color=#007700].[color=#0000bb]col3 FROM #temp A,
[color=#007700]([color=#0000bb]SELECT col1[color=#007700],[color=#0000bb]col2[color=#007700],[color=#0000bb]MAX[color=#007700]([color=#0000bb]id[color=#007700]) id
FROM #temp GROUP BY col1,col2 HAVING COUNT(*) > 1) B
[color=#0000bb]WHERE A[color=#007700].[color=#0000bb]ID [color=#007700] [color=#0000bb]B[color=#007700].[color=#0000bb]ID  [color=#007700]AND [color=#0000bb]A[color=#007700].[color=#0000bb]col1 [color=#007700]= [color=#0000bb]B[color=#007700].[color=#0000bb]col1 [color=#007700]AND [color=#0000bb]A[color=#007700].[color=#0000bb]col2 [color=#007700]= [color=#0000bb]B[color=#007700].
2、 有对照表。如果在某个应用逻辑中，每天都需要向表itpub中导入（用DTS工具）一定数额的数据，而这些新的待导入的数据可能与表itpub中的数据重复，因此，为了避免这种重复，我们可以在导入前做数据过滤。在我们的例子中，itpub-tmp是每天更新的数据表，只存放当天的数据，itpub表是数据总表，会随时间累加。
代码:
[color=#0000bb]
[color=#007700]--创建表
   [color=#007700]IF [color=#0000bb]EXISTS[color=#007700]([color=#0000bb]SELECT 1 FROM SYSOBJECTS WHERE name [color=#007700]= [color=#dd0000]';itpub'; [color=#007700]AND [color=#0000bb]type [color=#007700]= [color=#dd0000]';U';)
DROP TABLE itpub
GO
CREATE TABLE itpub[color=#007700]([color=#0000bb]id INT PRIMARY KEY [color=#007700],[color=#0000bb]col1 CHAR[color=#007700]([color=#0000bb]1[color=#007700]),[color=#0000bb]col2 CHAR[color=#007700]([color=#0000bb]2[color=#007700]),[color=#0000bb]col3 CHAR[color=#007700]([color=#0000bb]3))
GO
[color=#007700]--数据准备
INSERT INTO itpub SELECT 1[color=#007700],[color=#dd0000]';A';[color=#007700],[color=#dd0000]';B';[color=#007700],[color=#dd0000]';C'; UNION ALL
                  SELECT 2[color=#007700],[color=#dd0000]';A';[color=#007700],[color=#dd0000]';B';[color=#007700],[color=#dd0000]';F'; UNION ALL
                  SELECT 3[color=#007700],[color=#dd0000]';G';[color=#007700],[color=#dd0000]';H';[color=#007700],[color=#dd0000]';I'; UNION ALL
                  SELECT 4[color=#007700],[color=#dd0000]';D';[color=#007700],[color=#dd0000]';E';[color=#007700],[color=#dd0000]';F'; UNION ALL
                  SELECT 5[color=#007700],[color=#dd0000]';G';[color=#007700],[color=#dd0000]';H';[color=#007700],[color=#dd0000]';I'; UNION ALL
                  SELECT 6[color=#007700],[color=#dd0000]';D';[color=#007700],[color=#dd0000]';E';[color=#007700],';F';
[color=#007700]--创建表
[color=#007700]IF [color=#0000bb]EXISTS[color=#007700]([color=#0000bb]SELECT 1 FROM SYSOBJECTS WHERE name [color=#007700]= [color=#dd0000]';itpub_tmp'; [color=#007700]AND [color=#0000bb]type [color=#007700]= [color=#dd0000]';U';)
DROP TABLE itpub_tmp
GO
CREATE TABLE itpub_tmp[color=#007700]([color=#0000bb]id INT PRIMARY KEY [color=#007700],[color=#0000bb]col1 CHAR[color=#007700]([color=#0000bb]1[color=#007700]),[color=#0000bb]col2 CHAR[color=#007700]([color=#0000bb]2[color=#007700]),[color=#0000bb]col3 CHAR[color=#007700]([color=#0000bb]3))
GO
[color=#007700]--数据准备
INSERT INTO itpub_tmp SELECT 7[color=#007700],[color=#dd0000]';A';[color=#007700],[color=#dd0000]';B';[color=#007700],[color=#dd0000]';C'; UNION ALL
                      SELECT 8[color=#007700],[color=#dd0000]';A';[color=#007700],[color=#dd0000]';D';[color=#007700],[color=#dd0000]';F'; UNION ALL
                      SELECT 9[color=#007700],[color=#dd0000]';G';[color=#007700],[color=#dd0000]';H';[color=#007700],[color=#dd0000]';I'; UNION ALL
                      SELECT 10[color=#007700],[color=#dd0000]';B';[color=#007700],[color=#dd0000]';E';[color=#007700],';F';
[color=#007700]--[color=#0000bb]删除itpub表中与itpub[color=#007700]-tmp重复的记录
DELETE A FROM itpub A
WHERE EXISTS[color=#007700]([color=#0000bb]SELECT 1 FROM itpub_tmp B WHERE A[color=#007700].[color=#0000bb]col1 [color=#007700]= [color=#0000bb]B[color=#007700].[color=#0000bb]col1 [color=#007700]AND [color=#0000bb]A[color=#007700].[color=#0000bb]col2 [color=#007700]= [color=#0000bb]B[color=#007700].[color=#0000bb]col2[color=#007700])

在具体的应用中，如果是用DTS工具导数据，往往都是将两者结合起来，既先将最新数据表itpub-tmp自身去重复，然后以itpub-tmp表作为基表，查找并删除总表itpub中与itpub-tmp相重复的数据。
当然，所有的这些处理都可以通过游标操作来实现。