none
如何从SQL Server表中移除重复记录 RRS feed

  • 问题

  • 对于关系型数据库来说,重复的记录会破坏实体完整性。如果表中已经存在这样的记录,我们需要移除它们。
    2011年6月16日 2:01
    版主

答案

  • 如果你正在使用的是SQL Server 2000,那么请参考下面的两篇KB文章来从表中移除重复记录:

     

    如何删除 SQL Server 表中的重复行

    http://support.microsoft.com/kb/139444

     

    如何通过使用脚本从 SQL Server 表中删除重复行

    http://support.microsoft.com/kb/70956

     

    如果你使用的是SQL Server 2005以及后续版本,那么我们可以借助ROW_NUMBER函数和公共表表达式来移除这些重复记录。在下面的例子中,表中有3列。有些记录在Col1Col2中有相同的值,但是在Col3中有不同的值,也就是说表中存在一些“部分重复”的记录。这种情况下,我们可以保留那些在Col3中有最大或者最小值的记录,并删除其余的那些“部分重复“的记录。

     

    DECLARE @Table_1 TABLE (Col1 int,Col2 int,Col3 datetime)

     

    INSERT INTO @Table_1 VALUES (1,1,'2009-12-04')

    INSERT INTO @Table_1 VALUES (1,2,'2009-12-04')

    INSERT INTO @Table_1 VALUES (1,2,'2009-12-05')

    INSERT INTO @Table_1 VALUES (2,2,'2009-12-04')

    INSERT INTO @Table_1 VALUES (3,1,'2009-12-04')

    INSERT INTO @Table_1 VALUES (3,1,'2009-12-03')

     

    SELECT Col1,Col2,Col3 FROM @Table_1

     

    ;WITH CTE (Col1,Col2,Col3,RowNumber)

    AS

    (

           SELECT Col1,Col2,Col3,

           ROW_NUMBER() OVER (PARTITION BY Col1,Col2 ORDER BY Col3 DESC) AS 'RowNumber'

           FROM @Table_1

    )

     

    DELETE FROM CTE WHERE RowNumber>1

     

    SELECT Col1,Col2,Col3 FROM @Table_1

     

    如果存在完全重复的记录,也就是说这些记录在所有的列中都有相同的值,那么我们可以在ORDER BY子句中指定全部的列名:

     

    ;WITH CTE (Col1,Col2,Col3,RowNumber)

    AS

    (

           SELECT Col1,Col2,Col3,

           ROW_NUMBER() OVER (PARTITION BY Col1,Col2,Col3 ORDER BY Col1) AS 'RowNumber'

           FROM @Table_1

    )

     

     

    相关文档:

     

    ROW_NUMBER (Transact-SQL)
    http://msdn.microsoft.com/zh-cn/library/ms186734.aspx

    使用公用表表达式
    http://msdn.microsoft.com/zh-cn/library/ms190766.aspx

     

    2011年6月16日 2:01
    版主

全部回复

  • 如果你正在使用的是SQL Server 2000,那么请参考下面的两篇KB文章来从表中移除重复记录:

     

    如何删除 SQL Server 表中的重复行

    http://support.microsoft.com/kb/139444

     

    如何通过使用脚本从 SQL Server 表中删除重复行

    http://support.microsoft.com/kb/70956

     

    如果你使用的是SQL Server 2005以及后续版本,那么我们可以借助ROW_NUMBER函数和公共表表达式来移除这些重复记录。在下面的例子中,表中有3列。有些记录在Col1Col2中有相同的值,但是在Col3中有不同的值,也就是说表中存在一些“部分重复”的记录。这种情况下,我们可以保留那些在Col3中有最大或者最小值的记录,并删除其余的那些“部分重复“的记录。

     

    DECLARE @Table_1 TABLE (Col1 int,Col2 int,Col3 datetime)

     

    INSERT INTO @Table_1 VALUES (1,1,'2009-12-04')

    INSERT INTO @Table_1 VALUES (1,2,'2009-12-04')

    INSERT INTO @Table_1 VALUES (1,2,'2009-12-05')

    INSERT INTO @Table_1 VALUES (2,2,'2009-12-04')

    INSERT INTO @Table_1 VALUES (3,1,'2009-12-04')

    INSERT INTO @Table_1 VALUES (3,1,'2009-12-03')

     

    SELECT Col1,Col2,Col3 FROM @Table_1

     

    ;WITH CTE (Col1,Col2,Col3,RowNumber)

    AS

    (

           SELECT Col1,Col2,Col3,

           ROW_NUMBER() OVER (PARTITION BY Col1,Col2 ORDER BY Col3 DESC) AS 'RowNumber'

           FROM @Table_1

    )

     

    DELETE FROM CTE WHERE RowNumber>1

     

    SELECT Col1,Col2,Col3 FROM @Table_1

     

    如果存在完全重复的记录,也就是说这些记录在所有的列中都有相同的值,那么我们可以在ORDER BY子句中指定全部的列名:

     

    ;WITH CTE (Col1,Col2,Col3,RowNumber)

    AS

    (

           SELECT Col1,Col2,Col3,

           ROW_NUMBER() OVER (PARTITION BY Col1,Col2,Col3 ORDER BY Col1) AS 'RowNumber'

           FROM @Table_1

    )

     

     

    相关文档:

     

    ROW_NUMBER (Transact-SQL)
    http://msdn.microsoft.com/zh-cn/library/ms186734.aspx

    使用公用表表达式
    http://msdn.microsoft.com/zh-cn/library/ms190766.aspx

     

    2011年6月16日 2:01
    版主
  • 如果对于有几百万条数据的数据库来说,这个执行起来需要很长的时间吧,针对大量数据重复数据删除的话,有更好的代码么??

    2012年1月4日 2:19