update eval_infraction ei
#关联车,证,企业信息
LEFT JOIN
(
SELECT ci.VEH_STATUS,ci.VEH_TYPE_ONE,ci.VEH_TYPE_TWO,ci.TOTAL_KG,ci.PLATE_BRAND,ci.ENGINE_NUM,
ci.VEH_FRAMENUM,ci.VEH_COLOR,ci.ADDRESS,ci.VEHICLE_MASTER,ci.VEH_PLATE_NUM,ci.ENT_NAME,ci.ENT_CODE,
c.EXAM_DATE,c.TRADING_CARD,c.ENT_DLYSJYXKZ,c.BUS_SCOPE,e.address AS mesAddress,e.representative
FROM car_vehicle_info ci
LEFT JOIN car_vehicle_card c ON ci.ID = c.INFO_ID AND c.state = 1
LEFT JOIN ent_enterprise_info e ON ci.ENT_CODE = e.organ_code
) cvi
ON ei.vehicle_merge_plate = cvi.VEH_PLATE_NUM
#关联人员信息
LEFT JOIN wks_worker ww
ON ei.worker_papers_no = ww.papers_no
#关联执法人员信息
LEFT JOIN
(
SELECT e.administratorId,m.departmentId,e.administratorName,u.username,m.departmentName,e.plateNo
FROM eval_marshalling_plate e
LEFT JOIN
(
SELECT em.id,em.departmentId,ud.`name` AS departmentName
FROM
eval_marshalling em
LEFT JOIN u_department ud ON em.departmentId = ud.id
)m ON e.marshallingId = m.id
LEFT JOIN u_user u ON e.administratorId = u.id
WHERE e.administratorId IS NOT NULL
GROUP BY e.plateNo
)emp
ON ei.vehicle_merge_plate = emp.plateNo
SET
#车辆信息替换
ei.veh_status = cvi.VEH_STATUS,
ei.vehicle_typeone = cvi.VEH_TYPE_ONE,
ei.vehicle_type = cvi.VEH_TYPE_TWO,
ei.tonseat_num = cvi.TOTAL_KG,
ei.plate_brand = cvi.PLATE_BRAND,
ei.engine_num = cvi.ENGINE_NUM,
ei.veh_framenum = cvi.VEH_FRAMENUM,
ei.veh_color = cvi.VEH_COLOR,
ei.vehicle_master = cvi.VEHICLE_MASTER,
ei.mas_address = cvi.ADDRESS,
#人员信息替换
ei.worker_id_card = ww.id_card,
ei.worker_tel = ww.tel,
ei.worker_address = ww.reside_address,
ei.worker_sex = ww.sex,
ei.worker_grade = ww.star_level,
#证信息
ei.ent_jyxkztime = cvi.EXAM_DATE,
ei.mas_xkzpre = SUBSTRING(cvi.TRADING_CARD,1,1),
ei.mas_xkz = SUBSTRING(cvi.TRADING_CARD,3),
ei.ent_jyxkzpre = SUBSTRING(cvi.ENT_DLYSJYXKZ,1,1),
ei.ent_dlysjyxkz = SUBSTRING(cvi.ENT_DLYSJYXKZ,3),
ei.ent_busscope = cvi.BUS_SCOPE,
#企业信息
ei.ent_code = cvi.ENT_CODE,
ei.ent_name = cvi.ENT_NAME,
ei.mas_address = cvi.mesAddress,
ei.ent_corporate = cvi.representative,
#执法人员
ei.zf_id = emp.administratorId,
ei.zf_dept_id = emp.departmentId,
ei.zf_dept = emp.departmentName,
ei.zf_person = emp.administratorName,
ei.zf_username = emp.username,
#修改空值状态
ei.`status` = '未立案'
WHERE 1=1
AND (ei.`status` is NULL OR ei.`status` = '未立案' OR ei.`status` = '')
AND ei.infraction_time> '2017-01-01'
更新多个表的不同字段到一个表中
最新推荐文章于 2022-11-25 15:55:55 发布