-- ============================================
-- 修复车辆里程统计表中的重复数据
-- 问题: 同一车辆同一天存在多条统计记录
-- 原因: 历史数据或并发插入导致
-- 解决: 保留stats_id最大的记录,删除其他重复记录
-- ============================================

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

-- 2. 删除重复数据(保留stats_id最大的记录)
DELETE FROM tb_vehicle_mileage_stats
WHERE stats_id NOT IN (
    SELECT max_id FROM (
        SELECT MAX(stats_id) as max_id
        FROM tb_vehicle_mileage_stats
        GROUP BY vehicle_id, stat_date
    ) as temp
);

-- 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';

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