同步健康师业绩表品项分类和品项ID.sql
2.11 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
-- 同步健康师业绩表和科技老师业绩表中的品项分类和品项ID字段
-- 数据来源：通过关联的品项明细表获取
-- ============================================
-- 健康师业绩表同步
-- ============================================
-- 1. 开单健康师业绩表：从开单品项明细表（lq_kd_pxmx）同步
UPDATE lq_kd_jksyj kd
INNER JOIN lq_kd_pxmx px ON px.F_Id = kd.F_kdpxid
SET
kd.F_ItemCategory = px.F_ItemCategory,
kd.F_ItemId = px.px
WHERE kd.F_kdpxid IS NOT NULL;
-- 2. 耗卡健康师业绩表：从耗卡品项明细表（lq_xh_pxmx）同步
UPDATE lq_xh_jksyj xh
INNER JOIN lq_xh_pxmx px ON px.F_Id = xh.F_kdpxid
SET
xh.F_ItemCategory = px.F_ItemCategory,
xh.F_ItemId = px.px
WHERE xh.F_kdpxid IS NOT NULL;
-- 3. 退卡健康师业绩表：从退卡品项明细表（lq_hytk_mx）同步
-- 注意：F_CardReturn 关联到 lq_hytk_mx.F_Id，F_tkpxid 是项目资料表ID（品项ID）
UPDATE lq_hytk_jksyj tk
INNER JOIN lq_hytk_mx mx ON mx.F_Id = tk.F_CardReturn
SET
tk.F_ItemCategory = mx.F_ItemCategory,
tk.F_ItemId = mx.px
WHERE tk.F_CardReturn IS NOT NULL;
-- ============================================
-- 科技老师业绩表同步
-- ============================================
-- 4. 开单科技老师业绩表：从开单品项明细表（lq_kd_pxmx）同步
UPDATE lq_kd_kjbsyj kd
INNER JOIN lq_kd_pxmx px ON px.F_Id = kd.F_kdpxid
SET
kd.F_ItemCategory = px.F_ItemCategory,
kd.F_ItemId = px.px
WHERE kd.F_kdpxid IS NOT NULL;
-- 5. 耗卡科技老师业绩表：从耗卡品项明细表（lq_xh_pxmx）同步
UPDATE lq_xh_kjbsyj xh
INNER JOIN lq_xh_pxmx px ON px.F_Id = xh.F_hkpxid
SET
xh.F_ItemCategory = px.F_ItemCategory,
xh.F_ItemId = px.px
WHERE xh.F_hkpxid IS NOT NULL;
-- 6. 退卡科技老师业绩表：从退卡品项明细表（lq_hytk_mx）同步
-- 注意：F_CardReturn 关联到 lq_hytk_mx.F_Id，F_tkpxid 是项目资料表ID（品项ID）
UPDATE lq_hytk_kjbsyj tk
INNER JOIN lq_hytk_mx mx ON mx.F_Id = tk.F_CardReturn
SET
tk.F_ItemCategory = mx.F_ItemCategory,
tk.F_ItemId = mx.px
WHERE tk.F_CardReturn IS NOT NULL;