在工作和面试中,经常出现如何查询或者删除重复数据的问题,如果有主键,那还好办一点,如果没有主键,那就有点麻烦。
当一个表上没有辅助键时,如果使用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设定,查询所有数据
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作为筛选条件,可以避免所有数据被删除。
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 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联机丛书)
分享到:
相关推荐
Transact-SQL元编程——用T-SQL生成T-SQL.pdf
T-SQL核对清单——13条卓有成效的T-SQL实践.pdf
T-SQL性能调优秘籍——基于SQL Server 2012窗口函数,书中提及的网站已经打不开了,在github上找到了。路径在附件中。
该书解释并比较了sql server 2000和sql server 2005在数据库开发相关问题上的解决方案,深入讨论了sql server 2005中新增的t-sql编程特性,包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...
1、处理常见业务问题,如总计、间隔、...3、T-SQL性能调优秘笈:基于SQL Server 2012窗口函数》基于SQLServer2012,讨论了SQL窗口、窗口函数、排序集合函数、窗口函数的优化以及利用窗口函数的T-SQL解决方案等内容。
文件列表 Chapter 01 - SQL Windowing.sql Chapter 02 - A Detailed Look at Window Functions.sql Chapter 03 - Ordered Set Functions.sql ...Chapter 05 - T-SQL Solutions using Window Functions.sql TSQL2012.sql
Microsoft SQL Server 2008技术内幕:T-SQL查询
T-sql编程 Sql程序编写基础进阶 第5_3
该书解释并比较了SQL Server 2000和SQL Server 2005在数据库开发相关问题上的解决方案,深入讨论了SQL Server 2005中新增的T-SQL编程特性,包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解...
本书及其续篇——《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》介绍了SQL Server 2005中高级T-SQL查询、查询优化及编程相关的知识。这两本书侧重于解决实践中的常见问题,并讨论了解决这些问题的方法。它们...
[Apress] T-SQL 2012 程序员高级开发技术指南 (英文版) [Apress] Pro T-SQL 2012 Programmer's Guide (E-Book) ☆ 图书概要:☆ Pro T–SQL 2012 Programmer’s Guide is every developer’s key to making full ...
T-SQL性能调优秘笈-基于SQL Server 2012窗口函数,适合数据库进阶。
SQLServer2005技术内幕T-SQL查询的代码示例
《sql server 2005 技术内幕 T-SQL查询》 《sql server 2005 技术内幕 T-SQL程序设计》 网上大多的资源都是英文的,好容易找到中文的了,上传上来和大家分享。 这两本书都是中文的(很不容易哟),是高清晰的pdf...
《Microsoft SQL Server 2008技术内幕:T-SQL查询》一书的源代码及附录A内容。确信可下载,真实有效的内容
Microsoft SQL Server 2005技术内幕:T-SQL查询.part1
Microsoft SQL Server 2005技术内幕:T-SQL查询的源代码,主要是SQL脚本
Microsoft SQL Server 2008技术内幕 T-SQL 查询 一书中,第四章,索引优化章节的示例数据库脚本。
《Microsoft SQL Server 2008技术内幕:T-SQL查询》全面深入地介绍了Microsoft SQL Server 2008中高级T-SQL查询、性能优化等方面的内容,以及SQL Server 2008新增加的一些特性。主要内容包括SQL的基础理论、查询优化...