修复送洗记录金额为0的问题.sql
3.29 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
-- ============================================
-- 修复送洗记录金额为0的问题
-- ============================================
-- 说明：修复送出记录（F_FlowType = 0）中，单价和数量都不为0，但总价为0的异常记录
-- 修复逻辑：重新计算总价 = 数量 × 单价
--
-- 注意事项：
-- 1. 执行前请先备份数据库
-- 2. 执行前请先执行检查SQL，确认会修复的记录
-- 3. 执行后请执行验证SQL，确认修复结果
-- 4. 修复后需要重新计算相关的工资和股份数据
-- ============================================
-- ============================================
-- 第一步：检查需要修复的记录（执行修复前先执行此SQL查看会修复哪些记录）
-- ============================================
SELECT
F_Id as 记录ID,
F_BatchNumber as 批次号,
F_StoreId as 门店ID,
F_ProductType as 产品类型,
F_Quantity as 数量,
F_LaundryPrice as 单价,
F_TotalPrice as 当前总价,
(F_Quantity * F_LaundryPrice) as 应修复为总价,
F_CreateTime as 创建时间
FROM lq_laundry_flow
WHERE F_FlowType = 0
AND F_IsEffective = 1
AND F_TotalPrice = 0
AND F_Quantity > 0
AND F_LaundryPrice > 0
ORDER BY F_CreateTime;
-- ============================================
-- 第二步：统计需要修复的记录数量
-- ============================================
SELECT
COUNT(*) as 需要修复的记录数量,
SUM(F_Quantity * F_LaundryPrice) as 应修复的总金额
FROM lq_laundry_flow
WHERE F_FlowType = 0
AND F_IsEffective = 1
AND F_TotalPrice = 0
AND F_Quantity > 0
AND F_LaundryPrice > 0;
-- ============================================
-- 第三步：执行修复（确认无误后执行此SQL）
-- ============================================
UPDATE lq_laundry_flow
SET F_TotalPrice = F_Quantity * F_LaundryPrice
WHERE F_FlowType = 0
AND F_IsEffective = 1
AND F_TotalPrice = 0
AND F_Quantity > 0
AND F_LaundryPrice > 0;
-- ============================================
-- 第四步：验证修复结果（执行修复后执行此SQL确认修复结果）
-- ============================================
-- 4.1 检查是否还有异常记录
SELECT
COUNT(*) as 剩余异常记录数量
FROM lq_laundry_flow
WHERE F_FlowType = 0
AND F_IsEffective = 1
AND F_TotalPrice = 0
AND F_Quantity > 0
AND F_LaundryPrice > 0;
-- 4.2 查看修复后的记录（随机查看几条）
SELECT
F_Id as 记录ID,
F_BatchNumber as 批次号,
F_ProductType as 产品类型,
F_Quantity as 数量,
F_LaundryPrice as 单价,
F_TotalPrice as 修复后总价,
(F_Quantity * F_LaundryPrice) as 验证计算值,
CASE
WHEN F_TotalPrice = (F_Quantity * F_LaundryPrice) THEN '正确'
ELSE '异常'
END as 验证结果
FROM lq_laundry_flow
WHERE F_FlowType = 0
AND F_IsEffective = 1
AND F_TotalPrice > 0
AND F_CreateTime >= '2025-12-01'
AND F_CreateTime < '2026-01-01'
ORDER BY F_CreateTime DESC
LIMIT 20;
-- 4.3 统计修复后的总金额
SELECT
COUNT(*) as 修复后的记录数量,
SUM(F_TotalPrice) as 修复后的总金额
FROM lq_laundry_flow
WHERE F_FlowType = 0
AND F_IsEffective = 1
AND F_TotalPrice > 0
AND F_CreateTime >= '2025-12-01'
AND F_CreateTime < '2026-01-01';