LEFT JOIN UNION ALL的写法主要思路就是变更的历史数据闭链新增(包含变更)的还有没匹配到的数据开链SQL:SELECT zip.* ,zip.date_start ,if(di.employee_id is null,99991231,di.dt) as date_end FROM (select * from user_zip_di where date_end 99991231) zip LEFT JOIN update_data_table di on zip.employee_id di.employee_id UNION ALL SELECT * ,dt as date_start ,99991231 as date_end FROM update_data_table