修复冷忠翠金三角ID为空.sql
5.62 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
-- =============================================================================
-- 单人修复：冷忠翠（用户ID 13198577167）健康师业绩金三角ID为空
--
-- 背景：客户端异步取金三角ID，编辑入口未回填耗卡日期 / 接口未返回即提交时会落空，
-- 导致金三角业绩页少算，数据看板正常，两边对不上。
-- 口径：她 2025-11 至 2026-07 每月绑定均为「精英队」841835190497051909，
-- 按「用户ID + 业绩月份」到 lq_jinsanjiao_user 取当月绑定回填，仅补空值。
--
-- 默认范围：2026年7月（8 行 / ¥2711.33，即当前反馈的差异）
-- 全量范围：放开第 5 节
-- =============================================================================
SET @user_id = '13198577167';
SET @start_time = '2026-07-01';
SET @end_time = '2026-08-01';
-- =============================================================================
-- 1. 修复前预览
-- =============================================================================
SELECT hk.F_Id AS 耗卡单号,
hk.hksj AS 耗卡时间,
hk.mdmc AS 门店,
hk.hymc AS 会员,
j.F_ItemName AS 品项,
j.jksyj AS 消耗业绩,
j.F_jsjid AS 当前金三角ID,
ju.jsj_id AS 应回填金三角ID,
jsj.jsj AS 战队
FROM lq_xh_jksyj j
JOIN lq_xh_hyhk hk ON hk.F_Id = j.glkdbh
LEFT JOIN lq_jinsanjiao_user ju
ON ju.user_id = j.jkszh
AND ju.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
AND ju.F_DeleteMark = 0
LEFT JOIN lq_ycsd_jsj jsj ON jsj.F_Id = ju.jsj_id
WHERE j.F_IsEffective = 1
AND j.jkszh = @user_id
AND (j.F_jsjid IS NULL OR j.F_jsjid = '')
AND j.yjsj >= @start_time
AND j.yjsj < @end_time
ORDER BY hk.hksj;
-- =============================================================================
-- 2. 备份
-- =============================================================================
-- 注意：GTID 模式禁用 CREATE TABLE ... SELECT，必须先建表再 INSERT SELECT
DROP TABLE IF EXISTS bak_lzc_jsjid_20260729;
CREATE TABLE bak_lzc_jsjid_20260729 (
row_id VARCHAR(50) NOT NULL COMMENT '业绩行主键 lq_xh_jksyj.F_Id',
glkdbh VARCHAR(50) NULL COMMENT '关联耗卡单号',
jkszh VARCHAR(50) NULL COMMENT '健康师用户ID',
jksxm VARCHAR(50) NULL COMMENT '健康师姓名',
yjsj DATETIME NULL COMMENT '业绩时间',
jksyj DECIMAL(18, 2) NULL COMMENT '健康师业绩',
old_jsj_id VARCHAR(255) NULL COMMENT '修复前金三角ID',
PRIMARY KEY (row_id)
) COMMENT '冷忠翠金三角ID修复备份 20260729';
INSERT INTO bak_lzc_jsjid_20260729 (row_id, glkdbh, jkszh, jksxm, yjsj, jksyj, old_jsj_id)
SELECT j.F_Id, j.glkdbh, j.jkszh, j.jksxm, j.yjsj, j.jksyj, j.F_jsjid
FROM lq_xh_jksyj j
WHERE j.F_IsEffective = 1
AND j.jkszh = @user_id
AND (j.F_jsjid IS NULL OR j.F_jsjid = '')
AND j.yjsj >= @start_time
AND j.yjsj < @end_time;
SELECT COUNT(*) AS 备份行数, ROUND(SUM(jksyj), 2) AS 涉及业绩 FROM bak_lzc_jsjid_20260729;
-- =============================================================================
-- 3. 执行修复
-- =============================================================================
UPDATE lq_xh_jksyj j
JOIN lq_jinsanjiao_user ju
ON ju.user_id = j.jkszh
AND ju.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
AND ju.F_DeleteMark = 0
AND ju.jsj_id IS NOT NULL
AND ju.jsj_id <> ''
SET j.F_jsjid = ju.jsj_id
WHERE j.F_IsEffective = 1
AND j.jkszh = @user_id
AND (j.F_jsjid IS NULL OR j.F_jsjid = '')
AND j.yjsj >= @start_time
AND j.yjsj < @end_time;
-- =============================================================================
-- 4. 验证：修复后应全部归入「精英队」，无「未挂战队」行
-- =============================================================================
SELECT COALESCE(jsj.jsj, '未挂战队') AS 战队,
COUNT(*) AS 行数,
ROUND(SUM(j.jksyj), 2) AS 消耗业绩
FROM lq_xh_jksyj j
LEFT JOIN lq_ycsd_jsj jsj ON jsj.F_Id = j.F_jsjid
WHERE j.F_IsEffective = 1
AND j.jkszh = @user_id
AND j.yjsj >= @start_time
AND j.yjsj < @end_time
GROUP BY 战队;
-- =============================================================================
-- 5. 全量修复（可选）：她还有以下缺口，确认后放开执行
-- 耗卡 202511(5行/722.77) 202512(1行/120) 202601(5行/2199.92)
-- 202604(6行/1618.28) 202605(5行/1959.2) 202606(5行/0)
-- 开单 202601(1行/0) 202603(2行/1399.6) 202606(2行/0)
-- =============================================================================
-- UPDATE lq_xh_jksyj j
-- JOIN lq_jinsanjiao_user ju
-- ON ju.user_id = j.jkszh AND ju.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
-- AND ju.F_DeleteMark = 0 AND ju.jsj_id IS NOT NULL AND ju.jsj_id <> ''
-- SET j.F_jsjid = ju.jsj_id
-- WHERE j.F_IsEffective = 1 AND j.jkszh = @user_id
-- AND (j.F_jsjid IS NULL OR j.F_jsjid = '');
-- UPDATE lq_kd_jksyj j
-- JOIN lq_jinsanjiao_user ju
-- ON ju.user_id = j.jkszh AND ju.F_Month = DATE_FORMAT(j.yjsj, '%Y%m')
-- AND ju.F_DeleteMark = 0 AND ju.jsj_id IS NOT NULL AND ju.jsj_id <> ''
-- SET j.jsj_id = ju.jsj_id
-- WHERE j.F_IsEffective = 1 AND j.jkszh = @user_id
-- AND (j.jsj_id IS NULL OR j.jsj_id = '');
-- =============================================================================
-- 6. 回滚（仅在结果有误时执行）
-- =============================================================================
-- UPDATE lq_xh_jksyj j
-- JOIN bak_lzc_jsjid_20260729 k ON k.row_id = j.F_Id
-- SET j.F_jsjid = k.old_jsj_id;