sql server删除重复记录只保留一条

delete from sys_project
from sys_project as aa
left join (
select min(id) as id from sys_project
group by pro_no
) as bb
on aa.id = bb.id
where bb.id is null

这里就是通过左外连接查询出按照sysCode分组的所有重复或不重复的记录组中的第一条记录(通过min(id)找出id最小的一条),如果左外连接没有匹配的记录,则左外连接的记录是空的,即可以通过bb.id is null的条件过滤出多余的重复记录进行删除。

alter table sys_project add constraint sys_project_uni unique (prj_no)

留下评论