补挂金三角业绩_jsjid_全公司_202601-202607_v3_分批.sql
5.89 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
151
152
153
154
155
156
157
158
159
-- =============================================================================
-- 补挂金三角业绩 jsj_id / F_jsjid（2026年1–7月）— V3 全公司分批版
-- =============================================================================
-- 与 V2 区别：映射表按「健康师 + 业绩月份」匹配，可一次脚本覆盖多个月。
-- 生产迁库后可补挂规模（仅当月有 ACTIVE 绑定且字段为空）：
-- 开单：2439 条 | 耗卡：3891 条 | 合计约 6330 条
-- 若只修 7 月，请用：补挂金三角业绩_jsjid_202607_v2_分批.sql
-- =============================================================================
SET NAMES utf8mb4;
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 @range_start = '2026-01-01 00:00:00';
SET @range_end = '2026-07-31 23:59:59';
-- =============================================================================
-- 步骤 1：构建映射表（每人每月一条战队，与 GoldTriangleBindingHelper 一致）
-- =============================================================================
DROP TABLE IF EXISTS tmp_jsj_user_bind_multi;
CREATE TABLE tmp_jsj_user_bind_multi (
user_id VARCHAR(64) NOT NULL,
perf_month VARCHAR(6) NOT NULL,
jsj_id VARCHAR(64) NOT NULL,
team_name VARCHAR(128) NULL,
PRIMARY KEY (user_id, perf_month),
KEY idx_jsj_id (jsj_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO tmp_jsj_user_bind_multi (user_id, perf_month, jsj_id, team_name)
SELECT
x.user_id,
x.F_Month,
MIN(x.jsj_id) AS jsj_id,
MIN(j.jsj) AS team_name
FROM (
SELECT ju.user_id, ju.F_Month, ju.jsj_id
FROM lq_jinsanjiao_user ju
INNER JOIN (
SELECT user_id, F_Month, MAX(F_CreatorTime) AS max_ct
FROM lq_jinsanjiao_user
WHERE F_DeleteMark = 0
AND status = 'ACTIVE'
AND F_Month >= '202601'
AND F_Month <= '202607'
AND jsj_id IS NOT NULL
AND jsj_id <> ''
GROUP BY user_id, F_Month
) latest
ON latest.user_id = ju.user_id
AND latest.F_Month = ju.F_Month
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 >= '202601'
AND ju.F_Month <= '202607'
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, x.F_Month;
SELECT COUNT(*) AS bind_user_month_cnt FROM tmp_jsj_user_bind_multi;
-- =============================================================================
-- 步骤 2：待补数量（按月份）
-- =============================================================================
SELECT DATE_FORMAT(k.yjsj, '%Y%m') AS ym, COUNT(*) AS kd_todo
FROM lq_kd_jksyj k
INNER JOIN tmp_jsj_user_bind_multi b
ON b.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
AND b.perf_month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1
AND (k.jsj_id IS NULL OR k.jsj_id = '')
AND k.yjsj >= @range_start
AND k.yjsj <= @range_end
GROUP BY DATE_FORMAT(k.yjsj, '%Y%m')
ORDER BY ym;
SELECT DATE_FORMAT(x.yjsj, '%Y%m') AS ym, COUNT(*) AS xh_todo
FROM lq_xh_jksyj x
INNER JOIN tmp_jsj_user_bind_multi b
ON b.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
AND b.perf_month = DATE_FORMAT(x.yjsj, '%Y%m')
WHERE x.F_IsEffective = 1
AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
AND x.yjsj >= @range_start
AND x.yjsj <= @range_end
GROUP BY DATE_FORMAT(x.yjsj, '%Y%m')
ORDER BY ym;
-- =============================================================================
-- 步骤 3C：分批补开单（每次 100 行 —— 重复执行直到 kd_remain = 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_multi b2
ON b2.user_id = COALESCE(NULLIF(TRIM(k2.jkszh), ''), NULLIF(TRIM(k2.jks), ''))
AND b2.perf_month = DATE_FORMAT(k2.yjsj, '%Y%m')
WHERE k2.F_IsEffective = 1
AND (k2.jsj_id IS NULL OR k2.jsj_id = '')
AND k2.yjsj >= @range_start
AND k2.yjsj <= @range_end
ORDER BY k2.F_Id
LIMIT 100
) batch ON batch.F_Id = k.F_Id
SET k.jsj_id = batch.jsj_id;
SELECT COUNT(*) AS kd_remain
FROM lq_kd_jksyj k
INNER JOIN tmp_jsj_user_bind_multi b
ON b.user_id = COALESCE(NULLIF(TRIM(k.jkszh), ''), NULLIF(TRIM(k.jks), ''))
AND b.perf_month = DATE_FORMAT(k.yjsj, '%Y%m')
WHERE k.F_IsEffective = 1
AND (k.jsj_id IS NULL OR k.jsj_id = '')
AND k.yjsj >= @range_start
AND k.yjsj <= @range_end;
-- =============================================================================
-- 步骤 3D：分批补耗卡（每次 100 行 —— 重复执行直到 xh_remain = 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_multi b2
ON b2.user_id = COALESCE(NULLIF(TRIM(x2.jkszh), ''), NULLIF(TRIM(x2.jks), ''))
AND b2.perf_month = DATE_FORMAT(x2.yjsj, '%Y%m')
WHERE x2.F_IsEffective = 1
AND (x2.F_jsjid IS NULL OR x2.F_jsjid = '')
AND x2.yjsj >= @range_start
AND x2.yjsj <= @range_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_multi b
ON b.user_id = COALESCE(NULLIF(TRIM(x.jkszh), ''), NULLIF(TRIM(x.jks), ''))
AND b.perf_month = DATE_FORMAT(x.yjsj, '%Y%m')
WHERE x.F_IsEffective = 1
AND (x.F_jsjid IS NULL OR x.F_jsjid = '')
AND x.yjsj >= @range_start
AND x.yjsj <= @range_end;
-- =============================================================================
-- 步骤 4：清理（验证通过后）
-- =============================================================================
-- DROP TABLE IF EXISTS tmp_jsj_user_bind_multi;