-- ============================================
|
-- 修复车辆里程统计表中的重复数据
|
-- 问题: 同一车辆同一天存在多条统计记录
|
-- 原因: 历史数据或并发插入导致
|
-- 解决: 保留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`);
|
