SQL Server-删除表中重复数据

在执行语句时难免会输入相同的数据，这些重复记录不但没有用，还可能导致数据库占用大量资料或导致数据库查询索引无效。

如果在建表的时候没有设置主键或unique索引，那么插入完全重复的数据是不会报错的，结果就是无法修改记录行或者删除记录行。通过SQL语句可以实现删除表中重复数据。

所有字段均重复的记录

?

1 2 3 4 5 6 7 8 9 10 11

create table Student (Name char(1),                       Memo char(2)) go insert into Student values(N'A', N'A1'),       (N'A', N'A1'),       (N'A', N'A2'),       (N'B', N'B1'),       (N'B', N'B1') -- N'xxx' 标识将单引号中的xxx强制转换为Unicode编码的字符,在中文情况下不会出现乱码 go

方法一

?

1 2 3 4 5 6

select distinct * into #tmpt from Student -- 通过distinct取唯一数据存到一个本地临时表中 drop table Student select * into Student from #tmpt -- 把临时表复制到新的表 drop table #tmpt

方法二

?

1 2 3 4 5 6 7

alter table Student   add Id int identity(1,1)  -- 给表添加标识列 delete from Student   where Id not in (select min(Id) from Student group by Name) alter table Student   drop column Id select * from Student

　　

部分关键字段重复的记录

比如学号字段重复，而其他字段不一定重复。这种属于隐含错误，需要结合实际情况具体分析。

?

1 2 3 4 5 6 7 8 9 10 11

create table t (ID int,                 Name char(1),                 Memo char(2)) go insert into t values(1, N'A', N'A1'),       (2, N'A', N'A2'),       (3, N'A', N'A3'),       (4, N'B', N'B1'),       (5, N'B', N'B2') go

方法一

?

1 2 3 4

delete t from t a where ID not in (select min(ID) from t where Name = a.Name) select * from t go

　　

?

1 2 3 4

delete from t where ID not in (select min(ID) from t group by Name) select * from t go

　　

?

1 2 3 4

delete t from t a where ID <> (select top 1 ID from t where Name = a.Name order by ID) select * from t go

　　

?

1 2 3 4

delete t from t a where ID > any (select ID from t where Name = a.Name) select * from t go

　　

方法二

?

1 2 3 4

delete t from t a where exists (select 1 from t where Name = a.Name and ID < a.ID) select * from t go

　　

?

1 2 3 4

delete t from t a where (select count(*) from t where Name = a.Name and ID < a.ID) > 0 select * from t go

　　

方法三

?

1 2 3 4 5 6

delete t from t a left join (select min(ID) ID, Name from t group by Name) b on a.Name = b.Name and a.ID = b.ID where b.ID is null select * from t go

　　

设立主键的表中误输入重复记录

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

create table Employee (ID int primary key,                        Name char(20),                        Age int,                        Sex bit default 1) go insert into Employee values(1, 'James', 25, default),       (2, 'James', 25, default),       (3, 'James', 25, default),       (4, 'Lisa', 24, 0),       (5, 'Lisa', 24, 0),       (6, 'Lisa', 24, 0),       (7, 'Mirsa', 23, 0),       (8, 'Mirsa', 23, 0),       (9, 'Mirsa', 23, 0),       (10, 'John', 26, default),       (11, 'Abraham', 28, default),       (12, 'Lincoln', 30, default) go

　　

?

1 2 3 4

delete T from (select row_number() over (partition by Name order by (select 0)) as RowNumber, * from Employee) T where T.RowNumber > 1 select * from Employee go