删除一个表中的重复数据同时保留第一次插入那一条以及sql优化.docx

删除一个表中的重复数据同时保留第一次插入那一条以及sql优化?? ? ? ?业务：一个表中有很多数据（id为自增主键），在这些数据中有个别数据出现了重复的数据。?? ? ? ?目标：需要把这些重复数据删除同时保留第一次插入的那一条数据，还要保持其它的数据不受影响。?? ? ? ?解题过程：??12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667???????第一步：查出所有要保留的下来的数据的id（save_id）[sql] SELECTid assave_id? ??FROMyujing.alarm_event_info_snapshot aeis? ?whereaeis.event_id in????????(SELECTae.id? ??????????FROMyujing.alarm_event ae? ?????????whereae.event_uuid likeyuanwtj_%)? ?groupby(aeis.event_id)? 优化后：[sql] SELECTaeis.id assave_id? ??FROMyujing.alarm_event ae? ?rightjoinyujing.alarm_event_info_snapshot aeis? ????onaeis.event_id = ae.id? ?whereae.event_uuid likeyuanwtj_%??groupby(aeis.event_id)? ??????????第二步：获取所有相关数据的id（all_id）[sql] SELECTaeis.id asall_id? ??FROMyujing.alarm_event_info_snapshot aeis? ?whereaeis.event_id in????????(SELECTae.id? ??????????FROMyujing.alarm_event ae? ?????????whereae.event_uuid likeyuanwtj_%)? ?orderbyaeis.event_id? 优化后：?????? [sql] SELECTaeis.id asall_id? ??FROMyujing.alarm_event ae? ?rightjoinyujing.alarm_event_info_snapshot aeis? ????onaeis.event_id = ae.id? ?whereae.event_uuid likeyuanwtj_%??????????第三步：获取要删除的数据的id（del_id）[sql] selectad.all_id asdel_id? ??from(SELECTaeis.id asall_id? ??????????FROMyujing.alarm_event_info_snapshot aeis? ?????????whereaeis.event_id in????????????????(SELECTae.id? ??????????????????FROMyujing.alarm_event ae? ?????????????????whereae.event_uuid likeyuanwtj_%)) asad? ?wheread.all_id notin(SELECTid assave_id? ???????????????????????????FROMyujing.alarm_event_info_snapshot aeis? ??????????????????????????whereaeis.event_id in?????????????????????????????????(SELECTae.id? ???????????????????????????????????FROMyujing.alarm_event ae? ??????????????????????????????????whereae.event_uuid likeyuanwtj_%)? ??????????????????????????groupby(aeis.event_id))? 优化后：[sql] selectad.all_id asdel_id? ??from(SELECTaeis.id asall_id? ??????????FROMyujing.alarm_event ae? ?????????rightjoinyujing.alar