修复产品平均单价计算.sql
2.8 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
-- ============================================
-- 修复产品平均单价计算
-- ============================================
-- 说明：重新计算所有产品的平均单价，基于当前所有有效库存
-- 执行时间：2025年
-- ============================================
-- 重新计算所有产品的平均单价（基于所有有效库存）
UPDATE `lq_product` p
SET p.`F_AveragePrice` = (
SELECT
CASE
WHEN SUM(inv.`F_Quantity`) > 0 THEN
SUM(
CASE
WHEN inv.`F_FinalAmount` IS NOT NULL AND inv.`F_FinalAmount` > 0 THEN inv.`F_FinalAmount`
WHEN inv.`F_PurchaseUnitPrice` IS NOT NULL AND inv.`F_PurchaseUnitPrice` > 0 THEN inv.`F_PurchaseUnitPrice` * inv.`F_Quantity`
ELSE 0
END
) / SUM(inv.`F_Quantity`)
ELSE p.`F_Price`
END
FROM `lq_inventory` inv
WHERE inv.`F_ProductId` = p.`F_Id`
AND inv.`F_IsEffective` = 1
AND inv.`F_Quantity` > 0
)
WHERE EXISTS (
SELECT 1
FROM `lq_inventory` inv
WHERE inv.`F_ProductId` = p.`F_Id`
AND inv.`F_IsEffective` = 1
AND inv.`F_Quantity` > 0
);
-- 对于没有库存的产品，将平均单价设置为产品价格
UPDATE `lq_product` p
SET p.`F_AveragePrice` = p.`F_Price`
WHERE p.`F_AveragePrice` = 0 OR p.`F_AveragePrice` IS NULL
OR NOT EXISTS (
SELECT 1
FROM `lq_inventory` inv
WHERE inv.`F_ProductId` = p.`F_Id`
AND inv.`F_IsEffective` = 1
AND inv.`F_Quantity` > 0
);
-- 验证特定产品的平均单价（产品ID: 770163009904444677）
-- 应该显示：平均单价 = 200.00 (100+200+300)/3 = 600/3 = 200
SELECT
p.`F_Id` as ProductId,
p.`F_ProductName` as ProductName,
p.`F_Price` as Price,
p.`F_AveragePrice` as AveragePrice,
SUM(inv.`F_Quantity`) as TotalQuantity,
SUM(
CASE
WHEN inv.`F_FinalAmount` IS NOT NULL AND inv.`F_FinalAmount` > 0 THEN inv.`F_FinalAmount`
WHEN inv.`F_PurchaseUnitPrice` IS NOT NULL AND inv.`F_PurchaseUnitPrice` > 0 THEN inv.`F_PurchaseUnitPrice` * inv.`F_Quantity`
ELSE 0
END
) as TotalAmount,
CASE
WHEN SUM(inv.`F_Quantity`) > 0 THEN
SUM(
CASE
WHEN inv.`F_FinalAmount` IS NOT NULL AND inv.`F_FinalAmount` > 0 THEN inv.`F_FinalAmount`
WHEN inv.`F_PurchaseUnitPrice` IS NOT NULL AND inv.`F_PurchaseUnitPrice` > 0 THEN inv.`F_PurchaseUnitPrice` * inv.`F_Quantity`
ELSE 0
END
) / SUM(inv.`F_Quantity`)
ELSE p.`F_Price`
END as CalculatedAveragePrice
FROM `lq_product` p
LEFT JOIN `lq_inventory` inv ON inv.`F_ProductId` = p.`F_Id` AND inv.`F_IsEffective` = 1 AND inv.`F_Quantity` > 0
WHERE p.`F_Id` = '770163009904444677'
GROUP BY p.`F_Id`, p.`F_ProductName`, p.`F_Price`, p.`F_AveragePrice`;