T-SQL技术收集——删除重复数据

         在工作和面试中，经常出现如何查询或者删除重复数据的问题，如果有主键，那还好办一点，如果没有主键，那就有点麻烦。

         当一个表上没有辅助键时，如果使用SSMS界面来删除，就会报错（注意，本人测试环境是2012，所以界面可能会有所不一样，但是对结果没有任何影响）：

         因为在创建表后插入数据是没有做判断。但是在删除时，为了保证数据库的一致性，RDBMS还是会做判断从而拒绝执行这类操作。

说明：

         要解决这种问题，除了在设计的过程中做好之外，还可以在数据没有重复数据的情况下，使用ALTER TABLE ADD Constraint语句来增加约束。

         但是要删除现有的重复值，使用SSMS界面是无法实现的，就算能实现，当数据量到达一定程度，也是不现实的。此时只能使用T-SQL语句，搭配SET ROWCOUNT 1让数据的处理方式一次一行或这使用DELETE  TOP (1)的方式删除，注意，括号是必须的。

         SET ROWCOUNT { number | @number_var }：使 SQL Server 在返回指定的行数之后停止处理查询。如果需要取消限制，只需要使用SETROWCOUNT 0就可以。

       下面是例子：

       --使用set rowcount 3设定，查询所有数据

SETROWCOUNT  3

SELECT* FROM AdventureWorks.HumanResources.Department

--结果仅返回3行

         结果如下：

--使用set rowcount 3，修改所有数据

UPDATEAdventureWorks.HumanResources.Department

SETname=name

(3 行受影响)

--回复原有设置

SETROWCOUNT  0

 

最后使用TOP (N)设定搭配INSERT/UPDATE /DELETE ，注意这部分只适合2005以后。

--使用TOP(3)设置，查询所有数据，注意是要有括号的

SELECTTOP(3)* FROM AdventureWorks.HumanResources.Department

--结果返回3行

 

UPDATETOP(3)AdventureWorks.HumanResources.DepartmentSET name =name

(3 行受影响)

 

另外，SQLServer提供了一个系统函数@@ROWCOUNT来返回影响行数。以下是例子：

--使用@@rowcount系统函数返回影响行数

SELECTEmployeeID,Title

FROMAdventureWorks.HumanResources.Employee

WHERETitle LIKE '%Manager%'

GO

SELECT@@ROWCOUNT 'Result'

解决方法：

首先创建一个测试表和插入测试数据：

USE tempdb

GO

CREATE TABLEMyT

(

    [SID]   INT,

    sname   VARCHAR(10),

    sdt     DATETIME

)

GO

--插入测试数据

INSERT INTOMyT VALUES (1,'Lu','2012/01/01');

INSERT INTOMyT VALUES (1,'Lu','2012/07/08');

INSERT INTOMyT VALUES (1,'Lu','2012/04/03');

INSERT INTOMyT VALUES (2,'Tian','2012/03/01');

INSERT INTOMyT VALUES (2,'Tian','2012/05/09');

INSERT INTOMyT VALUES (2,'Tian','2012/01/01');

INSERT INTOMyT VALUES (3,'AD','2012/01/08');

INSERT INTOMyT VALUES (3,'AD','2012/03/01');

INSERT INTOMyT VALUES (4,'Sun','2012/02/01');

INSERT INTOMyT VALUES (1,'Lu','2012/01/01');

INSERT INTOMyT VALUES (1,'Lu','2012/07/08');

INSERT INTOMyT VALUES (1,'Lu','2012/04/03');

INSERT INTOMyT VALUES (2,'Tian','2012/03/01');

INSERT INTOMyT VALUES (2,'Tian','2012/05/09');

INSERT INTOMyT VALUES (2,'Tian','2012/01/01');

INSERT INTOMyT VALUES (3,'AD','2012/01/08');

INSERT INTOMyT VALUES (3,'AD','2012/03/01');

INSERT INTOMyT VALUES (4,'Sun','2012/02/01');

GO

 

第一种方法：

使用SET ROWCOUNT 1方法来删除重复数据：

需要搭配WHILE 1=1无限循环，搭配BREAK作为终止。针对找出来的重复数据，使用GROUP BY 和HAVING COUNT(1)>1作为筛选条件，可以避免所有数据被删除。

SET ROWCOUNT 1

WHILE 1=1

BEGIN

    DELETE FROM MyT

    WHERE [sid] IN

    (

        SELECT [sid] FROM MyT

        GROUP BY [sid],sname

        HAVING COUNT(1)>1

    )

    IF @@ROWCOUNT=0

    BREAK

END

SET ROWCOUNT 0

 

--可以发现，重复的数据已经删除

SELECT *FROM MyT

第二种方法：

使用DELETE TOP(N)方法，先把刚才插入测试数据的脚本再执行，可以多执行几次。DELETE TOP(1)可以用来替代SET ROWCOUNT 1：

WHILE 1=1

BEGIN

    DELETE TOP(1) FROM MyT

    WHERE [sid] IN

    (

        SELECT [sid] FROM MyT

        GROUP BY [sid],sname

        HAVING COUNT(1)>1

    )

    IF @@ROWCOUNT=0

    BREAK

END

 

结果和上面的一样。

 

扩充：保留最近的一行数据：

有时候不仅仅要去掉重复数据，也要保证剩下的是最新的数据（日期最大），此时可以借助索引，使用索引排序，然后把日期最小的那些删掉，只保留日期最大的那一笔。

--建立复合索引，利用索引将数据以编号和日期升序排序

CREATE INDEXIDX_DT ON MyT([sid],sdtASC)

GO

--修改删除语句，搭配with index查询提示

WHILE 1=1

BEGIN

    DELETE TOP(1) FROM MyT

    WHERE [sid] IN

    (

        SELECT [sid] FROM MyTWITH (INDEX(idx_dt))

        GROUP BY [sid],sname

        HAVING COUNT(1) >1

    )

    IF @@ROWCOUNT=0

    BREAK

END

 

查询结果：

SELECT *FROM MyT

 

注意：

为了向后兼容，括号在 SELECT 语句中是可选的。

我们建议您始终对 SELECT 语句中的 TOP 使用括号，这样，就可以与在 INSERT、UPDATE、MERGE 和 DELETE 语句中需要使用括号保持一致（在这种情况下括号是必需的）。

（出自SQL SERVER 2012联机丛书）