添加产品平均单价字段.sql
1.76 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
-- ============================================
-- 添加产品平均单价字段
-- ============================================
-- 说明：为产品表添加平均单价字段，用于维护加权平均成本
-- 执行时间：2025年
-- ============================================
-- 1. 添加平均单价字段到产品表
ALTER TABLE `lq_product`
ADD COLUMN `F_AveragePrice` decimal(18,2) DEFAULT 0.00 COMMENT '平均单价（加权平均成本，用于出库计价）' AFTER `F_Price`,
ADD INDEX `idx_average_price` (`F_AveragePrice`);
-- 2. 初始化历史数据的平均单价
-- 说明：对于已有库存的产品，根据当前库存计算初始平均单价
-- 计算公式：平均单价 = SUM(库存金额) / SUM(库存数量)
-- 其中，库存金额优先使用 F_FinalAmount，其次使用 F_PurchaseUnitPrice * F_Quantity
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
);
-- 3. 对于没有库存的产品，将平均单价设置为产品价格
UPDATE `lq_product` p
SET p.`F_AveragePrice` = p.`F_Price`
WHERE p.`F_AveragePrice` = 0 OR p.`F_AveragePrice` IS NULL;