c4040263
 “wangming”
feat: V2.0.2 会员标签...
|
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
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
|
-- =============================================================================
-- 补挂金三角业绩 jsj_id / F_jsjid（2026年7月）— V2 分批版【全公司】
-- =============================================================================
-- 范围说明（生产数据迁库后 2026-07-30 核对）：
-- - 7 月未挂：开单约 3339、耗卡约 3638（含大量「当月无战队绑定」历史行，勿全量盲补）
-- - 本脚本可安全补挂：开单 319、耗卡 891（当月有 ACTIVE 绑定且 jsj_id 为空）
-- - 虎啸龙吟仍待补：开单 3 + 耗卡 13 = 16 条（步骤 2 可先秒级验证）
--
-- 适用场景：
-- 全量 UPDATE + 关联子查询易触发 2013 Lost connection，本脚本改为：
-- 1) 先建「健康师→战队」映射临时表（只算一次）
-- 2) 每次 UPDATE LIMIT 100，可反复执行直到影响 0 行
-- 3) 仅补空值，不覆盖已有 jsj_id（支持跨战队、支持断点续跑）
--
-- 执行建议（Navicat / DBeaver）：
-- - 不要一次运行整个文件；按「步骤」分段选中执行
-- - 执行前可先跑：SET SESSION net_read_timeout = 600;
-- - 步骤 C/D 重复执行同一段，直到「影响行数 = 0」
-- =============================================================================
SET NAMES utf8mb4;
-- 可选：拉长会话超时，减少 2013
SET SESSION net_read_timeout = 600;
SET SESSION net_write_timeout = 600;
SET SESSION wait_timeout = 600;
SET SESSION innodb_lock_wait_timeout = 120;
SET @month_start = '2026-07-01 00:00:00';
SET @month_end = '2026-07-31 23:59:59';
SET @perf_month = '202607';
SET @huxiao_jsj = '841830228446676229'; -- 虎啸龙吟
-- =============================================================================
-- 步骤 0：断点检查（看上次是否已部分执行）
-- =============================================================================
SELECT 'bak_kd' AS item, COUNT(*) AS cnt
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bak_kd_jksyj_jsjid_202607'
UNION ALL
SELECT 'bak_xh', COUNT(*)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bak_xh_jksyj_jsjid_202607';
-- 虎啸龙吟三人 16 条关键行状态（空=未补，有值=已补）
SELECT 'kd' AS typ, k.F_Id, k.jksxm, k.jsj_id
FROM lq_kd_jksyj k
WHERE k.F_Id IN (
'849792273540449542','852022282741089541','852022282741089542'
)
UNION ALL
SELECT 'xh', x.F_Id, x.jksxm, x.F_jsjid
FROM lq_xh_jksyj x
WHERE x.F_Id IN (
'843749838901216517','843749838901216518','844872313659720965',
'846897478727894277','846652670822319365','846692856495080709',
'848020321473660165','848553719795549445','849587037194421509',
'850906583914251525','851690507896620293','851690507900814597',
'851690507905008901'
);
-- =============================================================================
-- 步骤 1：备份（仅未挂行，很快；若已存在可跳过）
-- =============================================================================
CREATE TABLE IF NOT EXISTS bak_kd_jksyj_jsjid_202607 (
F_Id VARCHAR(64) NOT NULL,
glkdbh VARCHAR(64) NULL,
jks VARCHAR(64) NULL,
jkszh VARCHAR(64) NULL,
jksxm VARCHAR(128) NULL,
yjsj DATETIME NULL,
jsj_id VARCHAR(64) NULL,
F_IsEffective INT NULL,
PRIMARY KEY (F_Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS bak_xh_jksyj_jsjid_202607 (
F_Id VARCHAR(64) NOT NULL,
glkdbh VARCHAR(64) NULL,
jks VARCHAR(64) NULL,
jkszh VARCHAR(64) NULL,
jksxm VARCHAR(128) NULL,
yjsj DATETIME NULL,
F_jsjid VARCHAR(64) NULL,
F_IsEffective INT NULL,
PRIMARY KEY (F_Id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO bak_kd_jksyj_jsjid_202607
SELECT k.F_Id, k.glkdbh, k.jks, k.jkszh, k.jksxm, k.yjsj, k.jsj_id, k.F_IsEffective
FROM lq_kd_jksyj k
WHERE k.F_IsEffective = 1
AND (k.jsj_id IS NULL OR k.jsj_id = '')
AND k.yjsj >= @month_start
AND k.yjsj <= @month_end;
INSERT IGNORE INTO bak_xh_jksyj_jsjid_202607
SELECT x.F_Id, x.glkdbh, x.jks, x.jkszh, x.jksxm, x.yjsj, x.F_jsjid, x.F_IsEffective
FROM lq_xh_jksyj x
WHERE x.F_IsEffective = 1
AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
AND x.yjsj >= @month_start
AND x.yjsj <= @month_end;
-- =============================================================================
-- 步骤 2：虎啸龙吟三人 16 条（秒级完成，建议先跑这段）
-- 跨战队单 849167374304150789：只补唐宇欣，不动柳全菊
-- =============================================================================
UPDATE lq_kd_jksyj
SET jsj_id = @huxiao_jsj
WHERE F_Id IN (
'849792273540449542',
'852022282741089541',
'852022282741089542'
)
AND F_IsEffective = 1
AND (jsj_id IS NULL OR jsj_id = '');
UPDATE lq_xh_jksyj
SET F_jsjid = @huxiao_jsj
WHERE F_Id IN (
'843749838901216517','843749838901216518','844872313659720965',
'846897478727894277','846652670822319365','846692856495080709',
'848020321473660165','848553719795549445','849587037194421509',
'850906583914251525','851690507896620293','851690507900814597',
'851690507905008901'
)
AND F_IsEffective = 1
AND (F_jsjid IS NULL OR F_jsjid = '');
-- =============================================================================
-- 步骤 3：构建映射表 tmp_jsj_user_bind_202607（全公司 7 月，每人一条）
-- =============================================================================
DROP TABLE IF EXISTS tmp_jsj_user_bind_202607;
CREATE TABLE tmp_jsj_user_bind_202607 (
user_id VARCHAR(64) NOT NULL,
jsj_id VARCHAR(64) NOT NULL,
team_name VARCHAR(128) NULL,
PRIMARY KEY (user_id),
KEY idx_jsj_id (jsj_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO tmp_jsj_user_bind_202607 (user_id, jsj_id, team_name)
SELECT
x.user_id,
MIN(x.jsj_id) AS jsj_id,
MIN(j.jsj) AS team_name
FROM (
SELECT ju.user_id, ju.jsj_id
FROM lq_jinsanjiao_user ju
INNER JOIN (
SELECT user_id, MAX(F_CreatorTime) AS max_ct
FROM lq_jinsanjiao_user
WHERE F_DeleteMark = 0
AND status = 'ACTIVE'
AND F_Month = @perf_month
AND jsj_id IS NOT NULL
AND jsj_id <> ''
GROUP BY user_id
) latest
ON latest.user_id = ju.user_id
AND latest.max_ct = ju.F_CreatorTime
INNER JOIN lq_ycsd_jsj j0
ON j0.F_Id = ju.jsj_id
AND j0.yf = ju.F_Month
WHERE ju.F_DeleteMark = 0
AND ju.status = 'ACTIVE'
AND ju.F_Month = @perf_month
AND ju.jsj_id IS NOT NULL
AND ju.jsj_id <> ''
) x
INNER JOIN lq_ycsd_jsj j ON j.F_Id = x.jsj_id
GROUP BY x.user_id;
-- 映射表行数预览（应有当月绑定健康师人数）
SELECT COUNT(*) AS bind_user_cnt FROM tmp_jsj_user_bind_202607;
-- =============================================================================
-- 步骤 4：待补数量（执行前后对比用）
-- =============================================================================
SELECT 'kd_todo' AS typ, COUNT(*) AS cnt
FROM lq_kd_jksyj k
INNER JOIN tmp_jsj_user_bind_202607 b
ON b.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
WHERE k.F_IsEffective = 1
AND (k.jsj_id IS NULL OR k.jsj_id = '')
AND k.yjsj >= @month_start
AND k.yjsj <= @month_end
UNION ALL
SELECT 'xh_todo', COUNT(*)
FROM lq_xh_jksyj x
INNER JOIN tmp_jsj_user_bind_202607 b
ON b.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
WHERE x.F_IsEffective = 1
AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
AND x.yjsj >= @month_start
AND x.yjsj <= @month_end;
-- =============================================================================
-- 步骤 5C：分批补开单（每次 100 行 —— 重复执行本段直到影响 0 行）
-- =============================================================================
UPDATE lq_kd_jksyj k
INNER JOIN (
SELECT k2.F_Id, b2.jsj_id
FROM lq_kd_jksyj k2
INNER JOIN tmp_jsj_user_bind_202607 b2
ON b2.user_id = COALESCE(NULLIF(TRIM(k2.jkszh), ''), NULLIF(TRIM(k2.jks), ''))
WHERE k2.F_IsEffective = 1
AND (k2.jsj_id IS NULL OR k2.jsj_id = '')
AND k2.yjsj >= @month_start
AND k2.yjsj <= @month_end
ORDER BY k2.F_Id
LIMIT 100
) batch ON batch.F_Id = k.F_Id
SET k.jsj_id = batch.jsj_id;
-- 查看还剩多少（应为 0 才停）
SELECT COUNT(*) AS kd_remain
FROM lq_kd_jksyj k
INNER JOIN tmp_jsj_user_bind_202607 b
ON b.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
WHERE k.F_IsEffective = 1
AND (k.jsj_id IS NULL OR k.jsj_id = '')
AND k.yjsj >= @month_start
AND k.yjsj <= @month_end;
-- =============================================================================
-- 步骤 5D：分批补耗卡（每次 100 行 —— 重复执行本段直到影响 0 行）
-- =============================================================================
UPDATE lq_xh_jksyj x
INNER JOIN (
SELECT x2.F_Id, b2.jsj_id
FROM lq_xh_jksyj x2
INNER JOIN tmp_jsj_user_bind_202607 b2
ON b2.user_id = COALESCE(NULLIF(TRIM(x2.jkszh), ''), NULLIF(TRIM(x2.jks), ''))
WHERE x2.F_IsEffective = 1
AND (x2.F_jsjid IS NULL OR x2.F_jsjid = '')
AND x2.yjsj >= @month_start
AND x2.yjsj <= @month_end
ORDER BY x2.F_Id
LIMIT 100
) batch ON batch.F_Id = x.F_Id
SET x.F_jsjid = batch.jsj_id;
SELECT COUNT(*) AS xh_remain
FROM lq_xh_jksyj x
INNER JOIN tmp_jsj_user_bind_202607 b
ON b.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
WHERE x.F_IsEffective = 1
AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
AND x.yjsj >= @month_start
AND x.yjsj <= @month_end;
-- =============================================================================
-- 步骤 6：验证（虎啸龙吟 7 月）
-- =============================================================================
SELECT j.jsj AS team_name, SUM(CAST(k.jksyj AS DECIMAL(18,2))) AS billing_total
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
WHERE k.jsj_id = @huxiao_jsj
AND k.F_IsEffective = 1
AND k.yjsj >= @month_start AND k.yjsj <= @month_end
AND k.jksyj IS NOT NULL AND k.jksyj <> '' AND k.jksyj <> '0';
-- 预期 billing_total（生产迁库后）：120756.00（修复前约 115576 + 待补 5180）
SELECT k.jksxm, SUM(CAST(k.jksyj AS DECIMAL(18,2))) AS billing_amt
FROM lq_kd_jksyj k
WHERE k.jsj_id = @huxiao_jsj
AND k.F_IsEffective = 1
AND k.yjsj >= @month_start AND k.yjsj <= @month_end
AND k.jksyj IS NOT NULL AND k.jksyj <> '' AND k.jksyj <> '0'
GROUP BY k.jksxm ORDER BY billing_amt DESC;
SELECT x.jksxm, SUM(x.jksyj) AS consume_amt
FROM lq_xh_jksyj x
WHERE x.F_jsjid = @huxiao_jsj
AND x.F_IsEffective = 1
AND x.yjsj >= @month_start AND x.yjsj <= @month_end
AND x.jksyj IS NOT NULL AND x.jksyj <> 0
GROUP BY x.jksxm ORDER BY consume_amt DESC;
-- 预期 consume 合计（生产迁库后）：84469.49（修复前约 78920.31 + 待补 5549.18）
-- 跨战队单：柳全菊=卧虎藏龙，唐宇欣=虎啸龙吟
SELECT k.jksxm, k.jsj_id, j.jsj
FROM lq_kd_jksyj k
LEFT JOIN lq_ycsd_jsj j ON j.F_Id = k.jsj_id
WHERE k.glkdbh = '849167374304150789' AND k.F_IsEffective = 1;
-- =============================================================================
-- 步骤 7：清理临时表（验证通过后）
-- =============================================================================
-- DROP TABLE IF EXISTS tmp_jsj_user_bind_202607;
-- =============================================================================
-- 回滚（慎用，按备份表恢复）
-- =============================================================================
-- UPDATE lq_kd_jksyj k
-- INNER JOIN bak_kd_jksyj_jsjid_202607 b ON b.F_Id = k.F_Id
-- SET k.jsj_id = b.jsj_id;
--
-- UPDATE lq_xh_jksyj x
-- INNER JOIN bak_xh_jksyj_jsjid_202607 b ON b.F_Id = x.F_Id
-- SET x.F_jsjid = b.F_jsjid;
|
