删除重复记录(Mysql,SqlServer,Sqlite)
Mysql中有重复的数据:
select resource_id,count(1) from t_resource_apptype_releation GROUP BY resource_id having count(1)>1 order by count(1) desc
删除一下吧:
delete a from t_resource_apptype_releation as a, ( select *,min(id) from t_resource_apptype_releation group by resource_id having count(1) > 1 ) as b where a.resource_id = b.resource_id and a.id > b.id;
创建一个唯一索引,防止再发生类似事件。
现在开始玩SQL SERVER
查看:
select resource_id,count(1) from t_resource_apptype_releation GROUP BY resource_id having count(1)>1 order by count(1) desc
删除:
delete aa from t_resource_apptype_releation aa where exists(select * from t_resource_apptype_releation where aa.id>id and resource_id=aa.resource_id)
Sqlite这样:
delete from T_RESOURCE_APPTYPE_RELEATION where id not in (select min(id) from T_RESOURCE_APPTYPE_RELEATION group by resource_id);
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步