not exists优化 mysql数据存在不插入

记一次sql优化

原代码

INSERT INTO DataAnalysis_HrmDataHistory
                              (estate,lockstate,delestate,creator,created,modifier,modified,name,employeeCode,sex,department_id,place_id,onDutyTime,outDutyTime,isDimission,birthday) 
                              SELECT estate,lockstate,delestate,creator,created,modifier,modified,chineseName,employeeCode,sex,department_id,subCompany,onDutyTime,outDutyTime,isDimission,birthday 
                              FROM
                             (
                                SELECT * FROM Hrm_HrmData 
                                WHERE (isDimission = 79 AND onDutyTime < date_add('2020-01-01', interval 1 MONTH))       #date_add('2020-01-01', interval 1 MONTH) = '2020-02-01' 往后推一个月 
                                OR (isDimission = 80 AND outDutyTime >= date_add('2020-01-01', interval 1 MONTH))
                             ) h  
                               WHERE  NOT EXISTS  
                            (
                                SELECT * FROM DataAnalysis_HrmDataHistory WHERE DataAnalysis_HrmDataHistory.employeeCode = h.employeeCode
                            )

 

 

INSERT INTO DataAnalysis_HrmDataHistory
                              (estate,lockstate,delestate,creator,created,modifier,modified,name,employeeCode,sex,department_id,place_id,onDutyTime,outDutyTime,isDimission,birthday) 
                              SELECT estate,lockstate,delestate,creator,created,modifier,modified,chineseName,employeeCode,sex,department_id,subCompany,onDutyTime,outDutyTime,isDimission,birthday 
                              FROM
                             (
                                SELECT * FROM Hrm_HrmData 
                                WHERE (isDimission = 79 AND onDutyTime < date_add('2020-01-01', interval 1 MONTH)) 
                                OR (isDimission = 80 AND outDutyTime >= date_add('2020-01-01', interval 1 MONTH))
                             ) h  
                               WHERE h.employeeCode not in                               #找一个唯一的东西 然后判断存在与否
                            (
                                SELECT employeeCode FROM DataAnalysis_HrmDataHistory
                            )