-- 修复车辆里程统计表中的重复数据
|
-- 执行前请先备份数据！
|
|
-- 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`);
|
