d8035736
 “wangming”
Refactor project ...
|
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
|
-- ============================================
-- 修复储扣表（lq_kd_deductinfo）中 F_ItemCategory 字段为空的数据
-- ============================================
-- 说明：此脚本用于修复储扣表中品项分类字段为空的历史数据
--
-- 问题原因：
-- 1. 更新开单时使用了错误的字段（item.DeductId 而不是 item.ItemId）来查询品项分类
-- 2. 如果 item.ItemId 为空或无效，查询会返回 null
--
-- 修复逻辑：
-- 通过 F_ItemId 关联 lq_xmzl 表，获取品项分类（qt2）并更新到 F_ItemCategory 字段
--
-- 注意事项：
-- - 只更新有效记录（F_IsEffective = 1）
-- - 只更新分类字段为空的记录
-- - 只更新品项分类存在且不为空的记录
-- ============================================
-- 1. 查看需要修复的记录数
-- ============================================
SELECT
COUNT(*) as TotalNullCount,
COUNT(CASE WHEN item.qt2 IS NOT NULL THEN 1 END) as CanFixCount
FROM lq_kd_deductinfo deduct
LEFT JOIN lq_xmzl item ON deduct.F_ItemId = item.F_Id
WHERE deduct.F_IsEffective = 1
AND deduct.F_ItemCategory IS NULL;
-- ============================================
-- 2. 修复历史数据：从项目资料表中获取品项分类
-- ============================================
UPDATE lq_kd_deductinfo deduct
INNER JOIN lq_xmzl item ON deduct.F_ItemId = item.F_Id
SET deduct.F_ItemCategory = item.qt2
WHERE deduct.F_IsEffective = 1
AND deduct.F_ItemCategory IS NULL
AND item.qt2 IS NOT NULL;
-- ============================================
-- 3. 验证修复结果
-- ============================================
-- 查看修复后的统计信息
SELECT
COUNT(*) as TotalCount,
COUNT(F_ItemCategory) as HasCategoryCount,
COUNT(*) - COUNT(F_ItemCategory) as NullCategoryCount
FROM lq_kd_deductinfo
WHERE F_IsEffective = 1;
-- 查看仍然为空的记录（需要人工处理）
SELECT
deduct.F_Id,
deduct.F_BillingId,
deduct.F_ItemId,
deduct.F_ItemName,
deduct.F_ItemCategory,
item.qt2 as ItemQt2,
CASE
WHEN item.F_Id IS NULL THEN '品项不存在'
WHEN item.qt2 IS NULL THEN '品项分类为空'
ELSE '其他原因'
END as Reason
FROM lq_kd_deductinfo deduct
LEFT JOIN lq_xmzl item ON deduct.F_ItemId = item.F_Id
WHERE deduct.F_IsEffective = 1
AND deduct.F_ItemCategory IS NULL
LIMIT 10;
|
