本文共 1305 字,大约阅读时间需要 4 分钟。
最近小咸儿根据业务需求,做一个定时同步数据的任务。
业务需求:
A表要定时将数据同步到B表中,但是这其中还涉及到了其他的表,所以第一步需要进行多表联查。
SELECT user_id, user_name, user_code, organization_nameFROM `tc_organization_user` AS tc LEFT JOIN tik_user AS tk ON tc.user_id = tk.id LEFT JOIN tc_organization AS tco ON tco.id = tc.organization_idWHERE organization_id = '*************'
将数据查询出来之后,第二步则需要添加记录,这时候需要注意的是,当记录存在时,则执行更新操作。这里就需要用到关键句 on duplicate key update
作用: 这个语句的作用就是,当执行SQL语句时,如果存在该记录则执行更新操作,如果不存在该记录则执行插入操作。
INSERT INTO tim_mentorship_user ( id, user_name, user_code, user_grade, create_time, update_time, operator, creator, remark, is_delete ) SELECT DISTINCT user_id, user_name, user_code, organization_name, tk.create_time, tk.update_time, tk.operator, tk.creator, tk.remark, tk.is_delete FROM tc_organization_user AS tc LEFT JOIN tik_user AS tk ON tc.user_id = tk.id LEFT JOIN tc_organization AS tco ON tco.id = tc.organization_id WHERE tk.is_delete = 0 AND organization_id = '**************' ON DUPLICATE KEY UPDATE user_name = tk.user_name, user_code = tk.user_code, user_grade = tco.organization_name, create_time = tk.create_time, update_time = now(), operator = tk.operator, creator = tk.creator, remark = tk.remark, is_delete = tk.is_delete
注意点: 使用该语句,需要注意两点:
感谢您的阅读~
转载地址:http://kjerb.baihongyu.com/