-- 更新现有的空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;