SQL Server-删除表中重复数据

本文主要是介绍SQL Server-删除表中重复数据，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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

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

1.所有字段均重复的记录。

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

方法一：

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

　　方法二：

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

2.部分关键字段重复的记录。

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

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

方法一：

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

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

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

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

方法二：

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

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

方法三：

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

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

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

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

这篇关于SQL Server-删除表中重复数据的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！