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

[發糞塗牆]

由于不知道怎么发图片，所以这贴只是简要，详细请看：
http://blog.csdn.net/dba_huangzj/article/details/7699166

在工作和面试中，经常出现如何查询或者删除重复数据的问题，如果有主键，那还好办一点，如果没有主键，那就有点麻烦。
当一个表上没有辅助键时，如果使用SSMS界面来删除，就会报错

因为在创建表后插入数据是没有做判断。但是在删除时，为了保证数据库的一致性，RDBMS还是会做判断从而拒绝执行这类操作。
说明：
要解决这种问题，除了在设计的过程中做好之外，还可以在数据没有重复数据的情况下，使用ALTER TABLE ADD Constraint语句来增加约束。
但是要删除现有的重复值，使用SSMS界面是无法实现的，就算能实现，当数据量到达一定程度，也是不现实的。此时只能使用T-SQL语句，搭配SET ROWCOUNT 1让数据的处理方式一次一行或这使用DELETE TOP (1)的方式删除，注意，括号是必须的。
SET ROWCOUNT { number | @number_var }：使 SQL Server 在返回指定的行数之后停止处理查询。如果需要取消限制，只需要使用SET ROWCOUNT 0就可以。
下面是例子：
--使用set rowcount 3设定，查询所有数据
SET ROWCOUNT 3
SELECT * FROM AdventureWorks.HumanResources.Department
--结果仅返回3行


--使用set rowcount 3，修改所有数据
UPDATE AdventureWorks.HumanResources.Department
SET name=name
(3 行受影响)
--回复原有设置
SET ROWCOUNT 0

最后使用TOP (N)设定搭配INSERT /UPDATE /DELETE ，注意这部分只适合2005以后。
--使用TOP(3)设置，查询所有数据，注意是要有括号的
SELECT TOP(3) * FROM AdventureWorks.HumanResources.Department
--结果返回3行


UPDATE TOP(3) AdventureWorks.HumanResources.Department SET name =name
(3 行受影响)

另外，SQLServer提供了一个系统函数@@ROWCOUNT来返回影响行数。以下是例子：
--使用@@rowcount系统函数返回影响行数
SELECT EmployeeID,Title
FROM AdventureWorks.HumanResources.Employee
WHERE Title LIKE '%Manager%'
GO
SELECT @@ROWCOUNT 'Result'

解决方法：
首先创建一个测试表和插入测试数据：
USE tempdb
GO
CREATE TABLE MyT
(
[SID] INT,
sname VARCHAR(10),
sdt DATETIME
)
GO
--插入测试数据
INSERT INTO MyT VALUES (1,'Lu','2012/01/01');
INSERT INTO MyT VALUES (1,'Lu','2012/07/08');
INSERT INTO MyT VALUES (1,'Lu','2012/04/03');
INSERT INTO MyT VALUES (2,'Tian','2012/03/01');
INSERT INTO MyT VALUES (2,'Tian','2012/05/09');
INSERT INTO MyT VALUES (2,'Tian','2012/01/01');
INSERT INTO MyT VALUES (3,'AD','2012/01/08');
INSERT INTO MyT VALUES (3,'AD','2012/03/01');
INSERT INTO MyT VALUES (4,'Sun','2012/02/01');
INSERT INTO MyT VALUES (1,'Lu','2012/01/01');
INSERT INTO MyT VALUES (1,'Lu','2012/07/08');
INSERT INTO MyT VALUES (1,'Lu','2012/04/03');
INSERT INTO MyT VALUES (2,'Tian','2012/03/01');
INSERT INTO MyT VALUES (2,'Tian','2012/05/09');
INSERT INTO MyT VALUES (2,'Tian','2012/01/01');
INSERT INTO MyT VALUES (3,'AD','2012/01/08');
INSERT INTO MyT VALUES (3,'AD','2012/03/01');
INSERT INTO MyT VALUES (4,'Sun','2012/02/01');
GO

第一种方法：
使用SET ROWCOUNT 1方法来删除重复数据：
需要搭配WHILE 1=1无限循环，搭配BREAK作为终止。针对找出来的重复数据，使用GROUP BY 和HAVING COUNT(1)>1作为筛选条件，可以避免所有数据被删除。
ET 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 INDEX IDX_DT ON MyT([sid],sdt ASC)
GO
--修改删除语句，搭配with index查询提示
WHILE 1=1
BEGIN
DELETE TOP(1) FROM MyT
WHERE [sid] IN
(
SELECT [sid] FROM MyT WITH (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联机丛书）

[--小F--]

[闹铃]

学习啦 新的思路 不错

[筱筱澄]

在以后的版本中将会去掉SET ROWCOUNT 功能

一般的重复，如保留id最大/最下 日期最大/最小的
可以直接

delete t from tb t where exists(select 1 from tb where name=t.name and id>t.id) 

删除行重复(出现多行数据都一样的)，可以使用row_number 生成行号后删除

; with t as
(
select *,row_id=row_number() over(partiton by name order by id ) from tb
)
delete from t where row_id>1

[發糞塗牆]

[Quote=引用 3 楼 的回复:]

在以后的版本中将会去掉SET ROWCOUNT 功能

一般的重复，如保留id最大/最下 日期最大/最小的
可以直接
SQL code

delete t from tb t where exists(select 1 from tb where name=t.name and id>t.id)


删除行重复(出现多行数据都一样的)，可以使用row_number 生成行号后删……
[/Quote]也不错的思路，但是没试过你的和我的性能上会不会对大数据量都不好。不过我觉得这种情况的发生几率比较小，一般都在开发初期，所以数据量都应该不会太大。

[叶子]

不错，支持一下。

[SQL777]

UPP一下。重复数据的删除一般都处理少量的.

[liangCK]

还是写点更好技术收集贴吧，这种东西，论坛的人都贴得满天飞了。

[發糞塗牆]

[Quote=引用 7 楼 的回复:]

还是写点更好技术收集贴吧，这种东西，论坛的人都贴得满天飞了。
[/Quote]谢谢提醒，我不否认这些东西早就有，但是就想行转列的问题一样，总会有很多人发帖。如果不能保证每个人都能找到以前的好帖，那么就偶尔发一下，这样最起码可以保证那段时间段附近的人都能解决他们的问题。我反而觉得偶尔有人发一下，能更好地帮助别人。

[lao_bulls]

支持一下