同步业绩表门店ID字段.sql
2.78 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
-- 同步6个业绩表的门店ID字段
-- 开单和消耗业绩表：通过开单编号（glkdbh）关联开单记录表（lq_kd_kdjlb），获取门店ID（djmd）
-- 退卡业绩表：通过退卡编号（gltkbh）关联退卡表（lq_hytk_hytk），获取门店ID（md）
-- 给业绩表的关联字段加索引
-- 注意：如果索引已存在会报错，可以忽略该错误继续执行后续UPDATE语句
-- 开单健康师业绩表
CREATE INDEX idx_kd_jksyj_glkdbh ON lq_kd_jksyj(glkdbh);
-- 开单科技老师业绩表
CREATE INDEX idx_kd_kjbsyj_glkdbh ON lq_kd_kjbsyj(glkdbh);
-- 消耗健康师业绩表
CREATE INDEX idx_xh_jksyj_glkdbh ON lq_xh_jksyj(glkdbh);
-- 消耗科技老师业绩表
CREATE INDEX idx_xh_kjbsyj_glkdbh ON lq_xh_kjbsyj(glkdbh);
-- 退卡健康师业绩表
CREATE INDEX idx_hytk_jksyj_gltkbh ON lq_hytk_jksyj(gltkbh);
-- 退卡科技老师业绩表
CREATE INDEX idx_hytk_kjbsyj_gltkbh ON lq_hytk_kjbsyj(gltkbh);
-- 1. 开单健康师业绩表：通过glkdbh关联开单记录表获取门店ID
UPDATE lq_kd_jksyj yj
INNER JOIN lq_kd_kdjlb kd ON yj.glkdbh = kd.F_Id
SET yj.F_StoreId = kd.djmd
WHERE yj.glkdbh IS NOT NULL
AND yj.glkdbh != ''
AND kd.djmd IS NOT NULL
AND kd.djmd != '';
-- 2. 开单科技老师业绩表：通过glkdbh关联开单记录表获取门店ID
UPDATE lq_kd_kjbsyj yj
INNER JOIN lq_kd_kdjlb kd ON yj.glkdbh = kd.F_Id
SET yj.F_StoreId = kd.djmd
WHERE yj.glkdbh IS NOT NULL
AND yj.glkdbh != ''
AND kd.djmd IS NOT NULL
AND kd.djmd != '';
-- 3. 消耗健康师业绩表：通过glkdbh关联开单记录表获取门店ID
UPDATE lq_xh_jksyj yj
INNER JOIN lq_xh_hyhk xh ON yj.glkdbh = xh.F_Id
SET yj.F_StoreId = xh.md
WHERE (yj.F_StoreId IS NULL OR yj.F_StoreId = '')
AND yj.glkdbh IS NOT NULL
AND yj.glkdbh != ''
AND xh.F_Id IS NOT NULL
AND xh.md IS NOT NULL
AND xh.md != ''
AND xh.F_IsEffective = 1;
-- 4. 消耗科技老师业绩表：通过glkdbh关联开单记录表获取门店ID
UPDATE lq_xh_kjbsyj yj
INNER JOIN lq_xh_hyhk xh ON yj.glkdbh = xh.F_Id
SET yj.F_StoreId = xh.md
WHERE (yj.F_StoreId IS NULL OR yj.F_StoreId = '')
AND yj.glkdbh IS NOT NULL
AND yj.glkdbh != ''
AND xh.F_Id IS NOT NULL
AND xh.md IS NOT NULL
AND xh.md != ''
AND xh.F_IsEffective = 1;
-- 5. 退卡健康师业绩表：通过gltkbh关联退卡表获取门店ID
UPDATE lq_hytk_jksyj yj
INNER JOIN lq_hytk_hytk tk ON yj.gltkbh = tk.F_Id
SET yj.F_StoreId = tk.md
WHERE yj.gltkbh IS NOT NULL
AND yj.gltkbh != ''
AND tk.md IS NOT NULL
AND tk.md != '';
-- 6. 退卡科技老师业绩表：通过gltkbh关联退卡表获取门店ID
UPDATE lq_hytk_kjbsyj yj
INNER JOIN lq_hytk_hytk tk ON yj.gltkbh = tk.F_Id
SET yj.F_StoreId = tk.md
WHERE yj.gltkbh IS NOT NULL
AND yj.gltkbh != ''
AND tk.md IS NOT NULL
AND tk.md != '';