更新历史数据业绩类型字段.sql
8.04 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
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
-- 更新历史数据的业绩类型字段
-- 业绩类型根据品项表（lq_xmzl）的 fl3 字段来设置
-- fl3 字段是"分类③-工资用"，用于区分不同的业绩类型
-- 1. 更新开单品项表（通过品项ID关联品项表的fl3字段）
UPDATE lq_kd_pxmx pxmx
INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
SET pxmx.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (pxmx.F_PerformanceType IS NULL OR pxmx.F_PerformanceType = '')
AND pxmx.F_IsEffective = 1;
-- 2. 更新开单健康师业绩表（通过品项ID或开单品项明细ID关联品项表的fl3字段）
UPDATE lq_kd_jksyj jksyj
LEFT JOIN lq_kd_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(jksyj.F_ItemId, pxmx.px) = xmzl.F_Id
SET jksyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (jksyj.F_PerformanceType IS NULL OR jksyj.F_PerformanceType = '')
AND jksyj.F_IsEffective = 1
AND xmzl.F_Id IS NOT NULL;
-- 3. 更新开单科技部老师业绩表（通过品项ID或开单品项明细ID关联品项表的fl3字段）
UPDATE lq_kd_kjbsyj kjbsyj
LEFT JOIN lq_kd_pxmx pxmx ON kjbsyj.F_kdpxid = pxmx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(kjbsyj.F_ItemId, pxmx.px) = xmzl.F_Id
SET kjbsyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (kjbsyj.F_PerformanceType IS NULL OR kjbsyj.F_PerformanceType = '')
AND kjbsyj.F_IsEffective = 1
AND xmzl.F_Id IS NOT NULL;
-- 4. 更新消耗品项表（通过品项ID关联品项表的fl3字段）
UPDATE lq_xh_pxmx pxmx
INNER JOIN lq_xmzl xmzl ON pxmx.px = xmzl.F_Id
SET pxmx.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (pxmx.F_PerformanceType IS NULL OR pxmx.F_PerformanceType = '')
AND (pxmx.F_IsEffective = 1 OR pxmx.F_IsEffective IS NULL);
-- 5. 更新消耗健康师业绩表（通过品项ID关联品项表的fl3字段）
UPDATE lq_xh_jksyj jksyj
LEFT JOIN lq_xh_pxmx pxmx ON jksyj.F_kdpxid = pxmx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(jksyj.F_ItemId, pxmx.px) = xmzl.F_Id
SET jksyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (jksyj.F_PerformanceType IS NULL OR jksyj.F_PerformanceType = '')
AND (jksyj.F_IsEffective = 1 OR jksyj.F_IsEffective IS NULL)
AND xmzl.F_Id IS NOT NULL;
-- 6. 更新消耗科技部老师业绩表（通过品项ID关联品项表的fl3字段）
UPDATE lq_xh_kjbsyj kjbsyj
LEFT JOIN lq_xh_pxmx pxmx ON kjbsyj.F_hkpxid = pxmx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(kjbsyj.F_ItemId, pxmx.px) = xmzl.F_Id
SET kjbsyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (kjbsyj.F_PerformanceType IS NULL OR kjbsyj.F_PerformanceType = '')
AND (kjbsyj.F_IsEffective = 1 OR kjbsyj.F_IsEffective IS NULL)
AND xmzl.F_Id IS NOT NULL;
-- 7. 更新退卡明细表（通过品项ID关联品项表的fl3字段）
UPDATE lq_hytk_mx mx
INNER JOIN lq_xmzl xmzl ON mx.px = xmzl.F_Id
SET mx.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (mx.F_PerformanceType IS NULL OR mx.F_PerformanceType = '')
AND mx.F_IsEffective = 1;
-- 8. 更新退卡健康师业绩表（通过品项ID关联品项表的fl3字段）
UPDATE lq_hytk_jksyj jksyj
LEFT JOIN lq_hytk_mx mx ON jksyj.F_CardReturn = mx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(jksyj.F_ItemId, mx.px) = xmzl.F_Id
SET jksyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (jksyj.F_PerformanceType IS NULL OR jksyj.F_PerformanceType = '')
AND jksyj.F_IsEffective = 1
AND xmzl.F_Id IS NOT NULL;
-- 9. 更新退卡科技部老师业绩表（通过品项ID关联品项表的fl3字段）
UPDATE lq_hytk_kjbsyj kjbsyj
LEFT JOIN lq_hytk_mx mx ON kjbsyj.F_CardReturn = mx.F_Id
LEFT JOIN lq_xmzl xmzl ON COALESCE(kjbsyj.F_ItemId, mx.px) = xmzl.F_Id
SET kjbsyj.F_PerformanceType = COALESCE(xmzl.fl3, '')
WHERE (kjbsyj.F_PerformanceType IS NULL OR kjbsyj.F_PerformanceType = '')
AND kjbsyj.F_IsEffective = 1
AND xmzl.F_Id IS NOT NULL;
-- 10. 更新业绩明细表（lq_yjmxb）
-- 业绩明细表通常是汇总表，可能包含开单和消耗的数据
-- 由于无法准确判断每条记录的具体来源，这里先设置为空
-- 如果需要更精确的更新，可以根据业务逻辑关联其他表来判断
-- 例如：如果业绩明细表有开单编号字段，可以关联开单表判断为'开单'
-- 如果业绩明细表有耗卡编号字段，可以关联耗卡表判断为'消耗'
UPDATE lq_yjmxb
SET F_PerformanceType = NULL
WHERE F_PerformanceType IS NULL OR F_PerformanceType = '';
-- 注意：如果业绩明细表有明确的关联字段，可以使用以下逻辑进行更精确的更新：
-- UPDATE lq_yjmxb yj
-- LEFT JOIN lq_kd_kdjlb kd ON yj.关联字段 = kd.F_Id
-- LEFT JOIN lq_xh_hyhk xh ON yj.关联字段 = xh.F_Id
-- SET yj.F_PerformanceType = CASE
-- WHEN kd.F_Id IS NOT NULL THEN '开单'
-- WHEN xh.F_Id IS NOT NULL THEN '消耗'
-- ELSE NULL
-- END
-- WHERE yj.F_PerformanceType IS NULL OR yj.F_PerformanceType = '';
-- 验证更新结果（统计各表的业绩类型分布）
SELECT
'lq_kd_pxmx' as table_name,
COUNT(*) as total_count,
SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_kd_pxmx
WHERE F_IsEffective = 1
UNION ALL
SELECT
'lq_kd_jksyj' as table_name,
COUNT(*) as total_count,
SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_kd_jksyj
WHERE F_IsEffective = 1
UNION ALL
SELECT
'lq_kd_kjbsyj' as table_name,
COUNT(*) as total_count,
SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_kd_kjbsyj
WHERE F_IsEffective = 1
UNION ALL
SELECT
'lq_xh_pxmx' as table_name,
COUNT(*) as total_count,
SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_xh_pxmx
WHERE F_IsEffective = 1 OR F_IsEffective IS NULL
UNION ALL
SELECT
'lq_xh_jksyj' as table_name,
COUNT(*) as total_count,
SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_xh_jksyj
WHERE F_IsEffective = 1 OR F_IsEffective IS NULL
UNION ALL
SELECT
'lq_xh_kjbsyj' as table_name,
COUNT(*) as total_count,
SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_xh_kjbsyj
WHERE F_IsEffective = 1 OR F_IsEffective IS NULL
UNION ALL
SELECT
'lq_hytk_mx' as table_name,
COUNT(*) as total_count,
SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_hytk_mx
WHERE F_IsEffective = 1
UNION ALL
SELECT
'lq_hytk_jksyj' as table_name,
COUNT(*) as total_count,
SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_hytk_jksyj
WHERE F_IsEffective = 1
UNION ALL
SELECT
'lq_hytk_kjbsyj' as table_name,
COUNT(*) as total_count,
SUM(CASE WHEN F_PerformanceType IS NOT NULL AND F_PerformanceType != '' THEN 1 ELSE 0 END) as '已设置',
SUM(CASE WHEN F_PerformanceType IS NULL OR F_PerformanceType = '' THEN 1 ELSE 0 END) as '未设置'
FROM lq_hytk_kjbsyj
WHERE F_IsEffective = 1;
-- 查看业绩类型的值分布（从品项表fl3字段）
SELECT
'lq_xmzl.fl3 值分布' as description,
fl3 as performance_type,
COUNT(*) as count
FROM lq_xmzl
WHERE fl3 IS NOT NULL AND fl3 != ''
GROUP BY fl3
ORDER BY count DESC;