-- ============================================================
|
-- 设备保养计划明细去重（数据修复，非结构变更）
|
-- 生成日期：2026-11-06
|
--
|
-- 背景：历史版本补设备/改日期时，可能给同一计划、同一设备、
|
-- 同一保养月份留下多条明细，导致导出文档重复。
|
--
|
-- 去重口径：同一 maintenance_plan_id + device_id + 保养月份
|
-- 只保留一条。
|
-- 优先保留已有保养记录的明细；都没有记录时保留最新一条。
|
-- 为保证数据安全，只删除“没有任何保养记录”的重复行；
|
-- 若同一组里有多条都已有保养记录，不自动删除，需人工确认。
|
--
|
-- 执行前必须先备份（脚本会自动创建备份表，已存在则不覆盖）。
|
-- 脚本可重复执行（幂等）。
|
-- ============================================================
|
|
-- 1. 备份明细表（首次执行创建，重复执行不覆盖）
|
CREATE TABLE IF NOT EXISTS `device_maintenance_plan_details_bak_20261106` AS
|
SELECT * FROM `device_maintenance_plan_details`;
|
|
-- 2. 查看去重前的重复情况
|
SELECT maintenance_plan_id, device_id, DATE_FORMAT(maintenance_date, '%Y-%m') AS maintenance_month,
|
COUNT(*) AS duplicate_count,
|
GROUP_CONCAT(maintenance_plan_detail_id ORDER BY maintenance_plan_detail_id) AS detail_ids
|
FROM device_maintenance_plan_details
|
GROUP BY maintenance_plan_id, device_id, DATE_FORMAT(maintenance_date, '%Y-%m')
|
HAVING COUNT(*) > 1;
|
|
-- 3. 删除重复行：按“有保养记录优先、最新优先”排序，仅删除无保养记录的重复行
|
START TRANSACTION;
|
|
DELETE d
|
FROM device_maintenance_plan_details d
|
JOIN (
|
SELECT maintenance_plan_detail_id
|
FROM (
|
SELECT dmpd.maintenance_plan_detail_id,
|
ROW_NUMBER() OVER (
|
PARTITION BY dmpd.maintenance_plan_id, dmpd.device_id, DATE_FORMAT(dmpd.maintenance_date, '%Y-%m')
|
ORDER BY (SELECT COUNT(*) FROM device_maintenance dm
|
WHERE dm.maintenance_plan_detail_id = dmpd.maintenance_plan_detail_id) DESC,
|
dmpd.maintenance_plan_detail_id DESC
|
) AS rn
|
FROM device_maintenance_plan_details dmpd
|
) ranked
|
WHERE ranked.rn > 1
|
) dup ON d.maintenance_plan_detail_id = dup.maintenance_plan_detail_id
|
WHERE NOT EXISTS (
|
SELECT 1 FROM device_maintenance dm
|
WHERE dm.maintenance_plan_detail_id = d.maintenance_plan_detail_id
|
);
|
|
COMMIT;
|
|
-- 4. 校验（执行完可人工核对）
|
-- 4.1 应为 0 行：同一计划 + 同一设备 + 同一保养月份仍有重复
|
-- SELECT maintenance_plan_id, device_id, DATE_FORMAT(maintenance_date, '%Y-%m') AS maintenance_month, COUNT(*) AS c
|
-- FROM device_maintenance_plan_details
|
-- GROUP BY maintenance_plan_id, device_id, DATE_FORMAT(maintenance_date, '%Y-%m')
|
-- HAVING c > 1;
|
--
|
-- 4.2 应为 0 行：保养记录关联的计划明细已不存在（防止误删导致记录孤儿）
|
-- SELECT dm.id, dm.maintenance_plan_detail_id
|
-- FROM device_maintenance dm
|
-- LEFT JOIN device_maintenance_plan_details dmpd
|
-- ON dmpd.maintenance_plan_detail_id = dm.maintenance_plan_detail_id
|
-- WHERE dmpd.maintenance_plan_detail_id IS NULL;
|
--
|
-- 4.3 清理前后数量对比：
|
-- SELECT COUNT(*) FROM device_maintenance_plan_details;
|
-- SELECT COUNT(*) FROM device_maintenance_plan_details_bak_20261106;
|
