2026-08-06-生产修复-C段-最后执行.sql
5.22 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
-- =============================================================================
-- 金三角修复 · 最后执行段（C 段）
-- 前置：已跑完 A 预览 + B 备份（四个 bak_20260806_* 表已存在）
-- 用法：整段选中执行 → 看 C5 三项 remain_* 必须全是 0 → 再执行 COMMIT
-- 若 C5 有非 0：执行 ROLLBACK
-- =============================================================================
START TRANSACTION;
SET SESSION group_concat_max_len = 102400;
DROP TEMPORARY TABLE IF EXISTS tmp_valid_jsj_binding;
CREATE TEMPORARY TABLE tmp_valid_jsj_binding AS
SELECT
x.user_id,
x.F_Month,
SUBSTRING_INDEX(GROUP_CONCAT(x.jsj_id ORDER BY x.F_CreatorTime DESC, x.F_Id DESC), ',', 1) AS jsj_id
FROM (
SELECT ju.user_id, ju.F_Month, ju.jsj_id, ju.F_Id, ju.F_CreatorTime
FROM lq_jinsanjiao_user ju
INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
) x
GROUP BY x.user_id, x.F_Month;
-- C1 软删幽灵 ACTIVE 绑定
UPDATE lq_jinsanjiao_user ju
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
SET ju.F_DeleteMark = 1,
ju.status = 'INACTIVE',
ju.F_LastModifyTime = NOW(),
ju.F_LastModifyUserId = 'prod-fix-20260806-orphan-binding'
WHERE ju.F_DeleteMark = 0
AND ju.status = 'ACTIVE'
AND j.F_Id IS NULL;
SELECT ROW_COUNT() AS c1_orphan_binding_updated;
-- C2 迁移幽灵业绩 → 有效战队
UPDATE lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
INNER JOIN tmp_valid_jsj_binding v
ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
SET k.jsj_id = v.jsj_id
WHERE k.F_IsEffective = 1
AND k.jsj_id IS NOT NULL AND k.jsj_id <> ''
AND j_old.F_Id IS NULL
AND v.jsj_id <> k.jsj_id;
SELECT ROW_COUNT() AS c2_kd_updated;
UPDATE lq_xh_jksyj x
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = x.F_jsjid
INNER JOIN tmp_valid_jsj_binding v
ON v.user_id = COALESCE(NULLIF(x.jks, ''), NULLIF(x.jkszh, ''))
AND v.F_Month = DATE_FORMAT(x.yjsj, '%Y%m')
SET x.F_jsjid = v.jsj_id
WHERE x.F_IsEffective = 1
AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> ''
AND j_old.F_Id IS NULL
AND v.jsj_id <> x.F_jsjid;
SELECT ROW_COUNT() AS c2_xh_updated;
UPDATE lq_hytk_jksyj h
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = h.F_jsjid
INNER JOIN tmp_valid_jsj_binding v
ON v.user_id = COALESCE(NULLIF(h.jks, ''), NULLIF(h.jkszh, ''))
AND v.F_Month = DATE_FORMAT(h.tksj, '%Y%m')
SET h.F_jsjid = v.jsj_id
WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> ''
AND j_old.F_Id IS NULL
AND v.jsj_id <> h.F_jsjid;
SELECT ROW_COUNT() AS c2_tk_updated;
-- C4 同人同月多 ACTIVE：只留最新
DROP TEMPORARY TABLE IF EXISTS tmp_keep_binding;
CREATE TEMPORARY TABLE tmp_keep_binding AS
SELECT ju.user_id, ju.F_Month,
SUBSTRING_INDEX(GROUP_CONCAT(ju.F_Id ORDER BY ju.F_CreatorTime DESC, ju.F_Id DESC), ',', 1) AS keep_id
FROM lq_jinsanjiao_user ju
INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
GROUP BY ju.user_id, ju.F_Month
HAVING COUNT(*) > 1;
DROP TEMPORARY TABLE IF EXISTS tmp_close_binding_ids;
CREATE TEMPORARY TABLE tmp_close_binding_ids AS
SELECT ju.F_Id
FROM lq_jinsanjiao_user ju
INNER JOIN tmp_keep_binding k
ON k.user_id = ju.user_id AND k.F_Month = ju.F_Month
INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
WHERE ju.F_DeleteMark = 0
AND ju.status = 'ACTIVE'
AND ju.F_Id <> k.keep_id;
UPDATE lq_jinsanjiao_user ju
INNER JOIN tmp_close_binding_ids c ON c.F_Id = ju.F_Id
SET ju.F_DeleteMark = 1,
ju.status = 'INACTIVE',
ju.F_LastModifyTime = NOW(),
ju.F_LastModifyUserId = 'prod-fix-20260806-multi-active';
SELECT ROW_COUNT() AS c4_multi_active_closed;
-- C5 验证（三项 cnt 必须全是 0）
SELECT 'remain_orphan_active_binding' AS check_item, COUNT(*) AS cnt
FROM lq_jinsanjiao_user ju
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE' AND j.F_Id IS NULL
UNION ALL
SELECT 'remain_migratable_orphan_kd', COUNT(*)
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
INNER JOIN tmp_valid_jsj_binding v
ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1 AND k.jsj_id <> '' AND j_old.F_Id IS NULL AND v.jsj_id <> k.jsj_id
UNION ALL
SELECT 'remain_multi_active', COUNT(*)
FROM (
SELECT ju.user_id, ju.F_Month
FROM lq_jinsanjiao_user ju
INNER JOIN lq_ycsd_jsj j ON j.F_Id = ju.jsj_id AND j.yf = ju.F_Month
WHERE ju.F_DeleteMark = 0 AND ju.status = 'ACTIVE'
GROUP BY ju.user_id, ju.F_Month
HAVING COUNT(*) > 1
) t;
-- 观察项（有值也允许 COMMIT，历史无法归队）
SELECT COUNT(*) AS orphan_kd_no_valid_binding
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
LEFT JOIN tmp_valid_jsj_binding v
ON v.user_id = COALESCE(NULLIF(k.jks, ''), NULLIF(k.jkszh, ''))
AND v.F_Month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1
AND k.jsj_id IS NOT NULL AND k.jsj_id <> ''
AND j.F_Id IS NULL
AND v.jsj_id IS NULL;
-- ★ C5 三项 remain_* 全是 0 后，单独执行下一行（不要和上面一起盲跑）★
-- COMMIT;
-- 若 C5 有非 0，执行：
-- ROLLBACK;