6f638734
 “wangming”
可以下班了，加油，冲
|
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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
|
-- =============================================================================
-- 金三角「幽灵战队」+ 业绩 jsj_id · 一次性执行脚本
-- 日期：2026-08-06 | GTID 环境（CREATE LIKE + INSERT 备份）
--
-- 【Navicat 分 3 次执行，避免漏 COMMIT】
-- 第1步：选中「第1步 A+B」整段 → 运行（预览 + 备份）
-- 第2步：选中「第2步 C」整段 → 运行（修复 + C5 验证）
-- 第3步：看 C5 三项 remain_* 全是 0 → 选中「第3步 COMMIT」→ 运行
-- 第4步：选中「第4步 提交后验证」→ 运行（抽检）
--
-- 【C5 任一项非 0】执行 ROLLBACK; 不要 COMMIT
--
-- 【首次跑预期 ROW_COUNT】c1≈18 c2_kd≈98 c2_xh≈355 c4≈1
-- 【已修复库再跑】c1/c2/c4 应为 0（幂等）
-- =============================================================================
-- #############################################################################
-- 第1步 A+B：预览 + 备份（事务外）
-- #############################################################################
-- A1~A6 预览
SELECT 'A1_orphan_active_binding' AS 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;
SELECT 'A2_orphan_kd' AS item, COUNT(*) AS cnt,
COALESCE(SUM(CAST(k.jksyj AS DECIMAL(18,2))), 0) AS amt
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
WHERE k.F_IsEffective = 1 AND k.jsj_id IS NOT NULL AND k.jsj_id <> '' AND j.F_Id IS NULL;
SELECT 'A3_orphan_xh' AS item, COUNT(*) AS cnt, COALESCE(SUM(x.jksyj), 0) AS amt
FROM lq_xh_jksyj x
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = x.F_jsjid
WHERE x.F_IsEffective = 1 AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> '' AND j.F_Id IS NULL;
SELECT 'A5_multi_active' AS item, COUNT(*) AS cnt
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;
SELECT 'A6_migratable_orphan_kd' AS item, COUNT(*) AS cnt,
COALESCE(SUM(CAST(k.jksyj AS DECIMAL(18,2))), 0) AS amt
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j_old ON j_old.F_Id = k.jsj_id
INNER JOIN (
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
) 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_old.F_Id IS NULL
AND v.jsj_id <> k.jsj_id;
-- B 备份
DROP TABLE IF EXISTS bak_20260806_jinsanjiao_user_orphan;
DROP TABLE IF EXISTS bak_20260806_kd_jksyj_orphan;
DROP TABLE IF EXISTS bak_20260806_xh_jksyj_orphan;
DROP TABLE IF EXISTS bak_20260806_hytk_jksyj_orphan;
CREATE TABLE bak_20260806_jinsanjiao_user_orphan LIKE lq_jinsanjiao_user;
INSERT INTO bak_20260806_jinsanjiao_user_orphan
SELECT ju.* 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;
CREATE TABLE bak_20260806_kd_jksyj_orphan LIKE lq_kd_jksyj;
INSERT INTO bak_20260806_kd_jksyj_orphan
SELECT k.* FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
WHERE k.F_IsEffective = 1 AND k.jsj_id IS NOT NULL AND k.jsj_id <> '' AND j.F_Id IS NULL;
CREATE TABLE bak_20260806_xh_jksyj_orphan LIKE lq_xh_jksyj;
INSERT INTO bak_20260806_xh_jksyj_orphan
SELECT x.* FROM lq_xh_jksyj x
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = x.F_jsjid
WHERE x.F_IsEffective = 1 AND x.F_jsjid IS NOT NULL AND x.F_jsjid <> '' AND j.F_Id IS NULL;
CREATE TABLE bak_20260806_hytk_jksyj_orphan LIKE lq_hytk_jksyj;
INSERT INTO bak_20260806_hytk_jksyj_orphan
SELECT h.* FROM lq_hytk_jksyj h
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = h.F_jsjid
WHERE (h.F_IsEffective = 1 OR h.F_IsEffective IS NULL)
AND h.F_jsjid IS NOT NULL AND h.F_jsjid <> '' AND j.F_Id IS NULL;
SELECT 'bak_user' AS t, COUNT(*) AS cnt FROM bak_20260806_jinsanjiao_user_orphan
UNION ALL SELECT 'bak_kd', COUNT(*) FROM bak_20260806_kd_jksyj_orphan
UNION ALL SELECT 'bak_xh', COUNT(*) FROM bak_20260806_xh_jksyj_orphan
UNION ALL SELECT 'bak_tk', COUNT(*) FROM bak_20260806_hytk_jksyj_orphan;
-- #############################################################################
-- 第2步 C：修复 + 验证（含 START TRANSACTION，本段末尾不要 COMMIT）
-- #############################################################################
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;
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;
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;
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：三项必须全是 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;
-- #############################################################################
-- 第3步：C5 三项全是 0 后，只选中下面 1 行执行
-- #############################################################################
COMMIT;
-- #############################################################################
-- 第4步：提交后验证（COMMIT 成功后再跑）
-- #############################################################################
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_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
UNION ALL
SELECT 'prod_fix_total', COUNT(*)
FROM lq_jinsanjiao_user
WHERE F_LastModifyUserId LIKE 'prod-fix-20260806%';
-- 郑美函 8 月：业绩应只在燃爆队
SELECT 'zheng_kd' AS item, jsj_id, COUNT(*) cnt, ROUND(SUM(CAST(jksyj AS DECIMAL(18,2))),2) amt
FROM lq_kd_jksyj
WHERE F_IsEffective = 1 AND yjsj >= '2026-08-01' AND yjsj < '2026-09-01'
AND (jks = '13989177235' OR jkszh = '13989177235')
GROUP BY jsj_id;
-- 燃爆队 8 月战队开单合计应含郑美函业绩
SELECT 'ranbao_team_kd' AS item, ROUND(SUM(CAST(jksyj AS DECIMAL(18,2))),2) AS team_billing
FROM lq_kd_jksyj
WHERE F_IsEffective = 1 AND jsj_id = '853054127826011397'
AND yjsj >= '2026-08-01' AND yjsj < '2026-09-01';
|
