Sql server 数据库查询重复的数据和删除重复的数据保留第一条数据

演示内容数据

数据库表
create table register(
	Id int primary key  identity,
	  [UserName] nvarchar(255),
	MobilePhone nvarchar(255),
    [Password] nvarchar(255)
);

插入测试数据  id是自增长的噢不用写
insert into register(UserName,MobilePhone,[Password]) values('1','137','1');
insert into register(UserName,MobilePhone,[Password]) values('2','136','2');
insert into register(UserName,MobilePhone,[Password]) values('3','150','3');
insert into register(UserName,MobilePhone,[Password]) values('4','183','5');
insert into register(UserName,MobilePhone,[Password]) values('5','101','6');

查询数据
select * from register;
go

查询插入数据结果

测试数据有了接下来开始我们的查询重复数据和删除重复数据保留第一条数据

查询指定字段重复的数据 --查询MobilePhone字段

select * from register where MobilePhone in(select MobilePhone from register group by MobilePhone having COUNT(MobilePhone) > 1);
go

查询结果 此时有的小伙伴就会想查询出来数据怎么没有ID等于209的那条数据呢 因为呀才有一条数据 MobilePhone字段没有重复所以不会被查询出来

查询指定多个字段重复的数据

select r.ID,r.UserName, r.MobilePhone,r.[Password] from register as r,(select MobilePhone,[Password] from register group by MobilePhone,[Password] having COUNT(1) > 1) as b where r.MobilePhone = b.MobilePhone and r.[Password] = b.[Password];
go

查询结果

删除重复的数据保留第一条

delete  register where ID not in(select MIN(ID) from register group by Password);

删除之后的结果

到此演示完啦 希望对你有所帮助 有什么问题可留言 记得点个赞噢！！！