MSSQL删除重复记录
Tags： SQL Server
👉 本文共约693个字，系统预计阅读时间或需3分钟。
1 2 3 4 5 6 7 8 9 | create table Student( ID varchar(10) not null, Name varchar(10) not null, ); insert into Student values('1', 'zhangs'); insert into Student values('2', 'zhangs'); insert into Student values('3', 'lisi'); insert into Student values('4', 'lisi'); insert into Student values('5', 'wangwu'); |
删除Name重复多余的行,每个Name仅保留1行数据
1、查询表中Name 重复的数据
1 | select Name from Student group by Name having count(Name) > 1 |
2、有唯一列，通过唯一列最大或最小方式删除重复记录
检查表中是否有主键或者唯一值的列，当前可以数据看到ID是唯一的，可以通过Name分组排除掉ID最大或最小的行
1 2 3 | delete from Student where Name in( select Name from Student group by Name having count(Name) > 1) and ID not in(select max(ID) from Student group by Name having count(Name) > 1 ) |
执行删除脚本后查询
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
