-- 修复车辆里程统计表中的重复数据
-- 执行前请先备份数据！

-- 1. 查看重复数据
SELECT vehicle_id, stat_date, COUNT(*) as count
FROM tb_vehicle_mileage_stats
GROUP BY vehicle_id, stat_date
HAVING COUNT(*) > 1;

-- 2. 删除重复记录，保留最新的一条（根据stats_id最大的保留）
DELETE t1 FROM tb_vehicle_mileage_stats t1
INNER JOIN (
    SELECT vehicle_id, stat_date, MAX(stats_id) as max_id
    FROM tb_vehicle_mileage_stats
    GROUP BY vehicle_id, stat_date
    HAVING COUNT(*) > 1
) t2 ON t1.vehicle_id = t2.vehicle_id 
    AND t1.stat_date = t2.stat_date 
    AND t1.stats_id < t2.max_id;

-- 3. 验证是否还有重复数据
SELECT vehicle_id, stat_date, COUNT(*) as count
FROM tb_vehicle_mileage_stats
GROUP BY vehicle_id, stat_date
HAVING COUNT(*) > 1;

-- 4. 确认唯一索引是否存在
SHOW INDEX FROM tb_vehicle_mileage_stats WHERE Key_name = 'uk_vehicle_date';

-- 如果唯一索引不存在，重新创建
-- ALTER TABLE tb_vehicle_mileage_stats 
-- ADD UNIQUE KEY `uk_vehicle_date` (`vehicle_id`, `stat_date`);