-- 更新现有的空vehicle_no数据
|
-- 从tb_vehicle_info表同步车牌号到tb_vehicle_gps_segment_mileage和tb_vehicle_mileage_stats
|
|
-- 1. 更新GPS分段里程表的vehicle_no
|
UPDATE tb_vehicle_gps_segment_mileage seg
|
INNER JOIN tb_vehicle_info v ON seg.vehicle_id = v.vehicle_id
|
SET seg.vehicle_no = v.vehicle_no
|
WHERE seg.vehicle_no IS NULL OR seg.vehicle_no = '';
|
|
-- 2. 更新里程统计表的vehicle_no
|
UPDATE tb_vehicle_mileage_stats stats
|
INNER JOIN tb_vehicle_info v ON stats.vehicle_id = v.vehicle_id
|
SET stats.vehicle_no = v.vehicle_no
|
WHERE stats.vehicle_no IS NULL OR stats.vehicle_no = '';
|
|
-- 查询更新结果
|
SELECT '更新GPS分段里程表' AS 表名, COUNT(*) AS 记录数,
|
SUM(CASE WHEN vehicle_no IS NOT NULL AND vehicle_no != '' THEN 1 ELSE 0 END) AS 已有车牌号,
|
SUM(CASE WHEN vehicle_no IS NULL OR vehicle_no = '' THEN 1 ELSE 0 END) AS 无车牌号
|
FROM tb_vehicle_gps_segment_mileage
|
UNION ALL
|
SELECT '更新里程统计表' AS 表名, COUNT(*) AS 记录数,
|
SUM(CASE WHEN vehicle_no IS NOT NULL AND vehicle_no != '' THEN 1 ELSE 0 END) AS 已有车牌号,
|
SUM(CASE WHEN vehicle_no IS NULL OR vehicle_no = '' THEN 1 ELSE 0 END) AS 无车牌号
|
FROM tb_vehicle_mileage_stats;
|
