lq_md_target_归属字段名称转组织ID.sql
8.67 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
-- lq_md_target 归属字段：将误存的组织「名称」统一回填为 base_organize.F_Id
-- 背景：规范要求 F_BusinessUnit / F_TechDepartment / F_EducationDepartment / F_MajorProjectDepartment 存组织ID
-- 混存原因见脚本末尾说明
-- ============================================================
-- 1. 修复前检查（先看再改）
-- ============================================================
-- 1.1 各字段「名称」vs「ID」数量
SELECT
SUM(CASE WHEN F_BusinessUnit REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS business_unit_id_cnt,
SUM(CASE WHEN F_BusinessUnit IS NOT NULL AND F_BusinessUnit != '' AND F_BusinessUnit NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS business_unit_name_cnt,
SUM(CASE WHEN F_TechDepartment REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS tech_id_cnt,
SUM(CASE WHEN F_TechDepartment IS NOT NULL AND F_TechDepartment != '' AND F_TechDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS tech_name_cnt,
SUM(CASE WHEN F_EducationDepartment REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS edu_id_cnt,
SUM(CASE WHEN F_EducationDepartment IS NOT NULL AND F_EducationDepartment != '' AND F_EducationDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS edu_name_cnt,
SUM(CASE WHEN F_MajorProjectDepartment REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS major_id_cnt,
SUM(CASE WHEN F_MajorProjectDepartment IS NOT NULL AND F_MajorProjectDepartment != '' AND F_MajorProjectDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS major_name_cnt
FROM lq_md_target;
-- 1.2 按月份查看（dev 库 202608 曾出现 28 条名称）
SELECT
F_Month,
SUM(CASE WHEN F_BusinessUnit IS NOT NULL AND F_BusinessUnit != '' AND F_BusinessUnit NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS business_unit_name_cnt
FROM lq_md_target
GROUP BY F_Month
HAVING business_unit_name_cnt > 0
ORDER BY F_Month DESC;
-- 1.3 无法匹配到组织的名称（应为 0 行；有结果则先人工处理）
SELECT DISTINCT t.F_BusinessUnit AS val, 'F_BusinessUnit' AS field
FROM lq_md_target t
WHERE t.F_BusinessUnit IS NOT NULL AND t.F_BusinessUnit != ''
AND t.F_BusinessUnit NOT REGEXP '^[0-9]{15,}$'
AND NOT EXISTS (
SELECT 1 FROM base_organize o
WHERE o.F_FullName = t.F_BusinessUnit
AND o.F_Category = 'department'
AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
)
UNION ALL
SELECT DISTINCT t.F_TechDepartment, 'F_TechDepartment'
FROM lq_md_target t
WHERE t.F_TechDepartment IS NOT NULL AND t.F_TechDepartment != ''
AND t.F_TechDepartment NOT REGEXP '^[0-9]{15,}$'
AND NOT EXISTS (
SELECT 1 FROM base_organize o
WHERE o.F_FullName = t.F_TechDepartment
AND o.F_Category = 'department'
AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
)
UNION ALL
SELECT DISTINCT t.F_EducationDepartment, 'F_EducationDepartment'
FROM lq_md_target t
WHERE t.F_EducationDepartment IS NOT NULL AND t.F_EducationDepartment != ''
AND t.F_EducationDepartment NOT REGEXP '^[0-9]{15,}$'
AND NOT EXISTS (
SELECT 1 FROM base_organize o
WHERE o.F_FullName = t.F_EducationDepartment
AND o.F_Category = 'department'
AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
)
UNION ALL
SELECT DISTINCT t.F_MajorProjectDepartment, 'F_MajorProjectDepartment'
FROM lq_md_target t
WHERE t.F_MajorProjectDepartment IS NOT NULL AND t.F_MajorProjectDepartment != ''
AND t.F_MajorProjectDepartment NOT REGEXP '^[0-9]{15,}$'
AND NOT EXISTS (
SELECT 1 FROM base_organize o
WHERE o.F_FullName = t.F_MajorProjectDepartment
AND o.F_Category = 'department'
AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
);
-- 1.4 预览：名称 -> 将回填的 ID
SELECT
t.F_Id,
t.F_Month,
t.F_StoreId,
t.F_BusinessUnit AS old_business_unit,
ob.F_Id AS new_business_unit,
t.F_TechDepartment AS old_tech,
ot.F_Id AS new_tech,
t.F_EducationDepartment AS old_edu,
oe.F_Id AS new_edu,
t.F_MajorProjectDepartment AS old_major,
om.F_Id AS new_major
FROM lq_md_target t
LEFT JOIN base_organize ob ON ob.F_FullName = t.F_BusinessUnit AND ob.F_Category = 'department' AND (ob.F_DeleteMark IS NULL OR ob.F_DeleteMark != 1)
LEFT JOIN base_organize ot ON ot.F_FullName = t.F_TechDepartment AND ot.F_Category = 'department' AND (ot.F_DeleteMark IS NULL OR ot.F_DeleteMark != 1)
LEFT JOIN base_organize oe ON oe.F_FullName = t.F_EducationDepartment AND oe.F_Category = 'department' AND (oe.F_DeleteMark IS NULL OR oe.F_DeleteMark != 1)
LEFT JOIN base_organize om ON om.F_FullName = t.F_MajorProjectDepartment AND om.F_Category = 'department' AND (om.F_DeleteMark IS NULL OR om.F_DeleteMark != 1)
WHERE (
(t.F_BusinessUnit IS NOT NULL AND t.F_BusinessUnit != '' AND t.F_BusinessUnit NOT REGEXP '^[0-9]{15,}$')
OR (t.F_TechDepartment IS NOT NULL AND t.F_TechDepartment != '' AND t.F_TechDepartment NOT REGEXP '^[0-9]{15,}$')
OR (t.F_EducationDepartment IS NOT NULL AND t.F_EducationDepartment != '' AND t.F_EducationDepartment NOT REGEXP '^[0-9]{15,}$')
OR (t.F_MajorProjectDepartment IS NOT NULL AND t.F_MajorProjectDepartment != '' AND t.F_MajorProjectDepartment NOT REGEXP '^[0-9]{15,}$')
)
ORDER BY t.F_Month DESC, t.F_StoreId;
-- ============================================================
-- 2. 执行回填（确认预览无误后再执行）
-- ============================================================
-- 2.1 事业部
UPDATE lq_md_target t
INNER JOIN base_organize o
ON t.F_BusinessUnit = o.F_FullName
AND o.F_Category = 'department'
AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
SET t.F_BusinessUnit = o.F_Id
WHERE t.F_BusinessUnit IS NOT NULL
AND t.F_BusinessUnit != ''
AND t.F_BusinessUnit NOT REGEXP '^[0-9]{15,}$';
-- 2.2 科技部
UPDATE lq_md_target t
INNER JOIN base_organize o
ON t.F_TechDepartment = o.F_FullName
AND o.F_Category = 'department'
AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
SET t.F_TechDepartment = o.F_Id
WHERE t.F_TechDepartment IS NOT NULL
AND t.F_TechDepartment != ''
AND t.F_TechDepartment NOT REGEXP '^[0-9]{15,}$';
-- 2.3 教育部
UPDATE lq_md_target t
INNER JOIN base_organize o
ON t.F_EducationDepartment = o.F_FullName
AND o.F_Category = 'department'
AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
SET t.F_EducationDepartment = o.F_Id
WHERE t.F_EducationDepartment IS NOT NULL
AND t.F_EducationDepartment != ''
AND t.F_EducationDepartment NOT REGEXP '^[0-9]{15,}$';
-- 2.4 大项目部
UPDATE lq_md_target t
INNER JOIN base_organize o
ON t.F_MajorProjectDepartment = o.F_FullName
AND o.F_Category = 'department'
AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
SET t.F_MajorProjectDepartment = o.F_Id
WHERE t.F_MajorProjectDepartment IS NOT NULL
AND t.F_MajorProjectDepartment != ''
AND t.F_MajorProjectDepartment NOT REGEXP '^[0-9]{15,}$';
-- ============================================================
-- 3. 修复后验证（名称应为 0）
-- ============================================================
SELECT
SUM(CASE WHEN F_BusinessUnit IS NOT NULL AND F_BusinessUnit != '' AND F_BusinessUnit NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS business_unit_name_cnt,
SUM(CASE WHEN F_TechDepartment IS NOT NULL AND F_TechDepartment != '' AND F_TechDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS tech_name_cnt,
SUM(CASE WHEN F_EducationDepartment IS NOT NULL AND F_EducationDepartment != '' AND F_EducationDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS edu_name_cnt,
SUM(CASE WHEN F_MajorProjectDepartment IS NOT NULL AND F_MajorProjectDepartment != '' AND F_MajorProjectDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS major_name_cnt
FROM lq_md_target;
-- ============================================================
-- 混存原因说明（排查结论）
-- ============================================================
-- 1. 规范：归属字段应存 base_organize.F_Id（项目文档、日报/驾驶舱 SQL 均按 ID 关联）
-- 2. 历史数据：202601~202607 基本全是 ID；仅 202608 出现 28 条名称（dev 库实测）
-- 3. 直接原因：列表接口 EnrichExportNamesAsync 把 ID 转成名称返回给前端；
-- 管理后台/门店 PC 编辑时直接用列表行回填表单，保存时把「事业一部」等名称写回库
-- 4. 后端 Create/Update 未做 ID 归一化，字符串原样入库
-- 5. 「同步上月」会原样复制上月值，若上月已是名称会继续扩散
--
-- 组织名称与 ID 对照（base_organize，department）：
-- 事业一部 734725158358484229 | 事业二部 734725192445592837 | 事业三部 734725229380633861
-- 事业四部 734725299018663173 | 事业五部 734725363116016901 | 事业六部 735107948883215621
-- 科技一部 734725579919590661 | 科技二部 734725628560934149
-- 教育一部 734725416912160005 | 教育二部 734725478136415493
-- 大项目一部 734725667203056901 | 大项目二部 734725746831918341