查询卸妆水库存入库领用数据核对.sql
4.06 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
-- ============================================
-- 卸妆水库存、入库、领用数据核对
-- ============================================
-- 说明：检查卸妆水的当前库存、入库总量、领用总量，验证数据是否对齐
-- 平衡关系：入库总量 = 当前库存 + 已领取数量
-- ============================================
-- 一、查找卸妆水产品
SELECT
F_Id AS 产品ID,
F_ProductName AS 产品名称,
F_Price AS 价格,
F_AveragePrice AS 平均单价
FROM lq_product
WHERE F_ProductName LIKE '%卸妆水%';
-- 二、卸妆水库存/入库/领用汇总（按产品）
-- 说明：领用数量只统计「已领取」(F_IsReceived=1) 的申请批次
SELECT
p.F_Id AS 产品ID,
p.F_ProductName AS 产品名称,
IFNULL(inv.入库总量, 0) AS 入库总量,
IFNULL(recv.已领取数量, 0) AS 已领取数量,
IFNULL(inv.入库总量, 0) - IFNULL(recv.已领取数量, 0) AS 当前可用库存,
CASE
WHEN IFNULL(inv.入库总量, 0) = IFNULL(recv.已领取数量, 0) + (IFNULL(inv.入库总量, 0) - IFNULL(recv.已领取数量, 0))
THEN '✅ 对齐'
ELSE '❌ 异常'
END AS 数据是否对齐
FROM lq_product p
LEFT JOIN (
-- 入库总量（有效库存记录）
SELECT
F_ProductId,
SUM(F_Quantity) AS 入库总量
FROM lq_inventory
WHERE F_IsEffective = 1
GROUP BY F_ProductId
) inv ON inv.F_ProductId = p.F_Id
LEFT JOIN (
-- 已领取数量（仅统计 F_IsReceived=1 的申请批次下的使用记录）
SELECT
u.F_ProductId,
SUM(u.F_UsageQuantity) AS 已领取数量
FROM lq_inventory_usage u
INNER JOIN lq_inventory_usage_application a
ON a.F_UsageBatchId = u.F_UsageBatchId
AND a.F_IsEffective = 1
AND a.F_IsReceived = 1
WHERE u.F_IsEffective = 1
GROUP BY u.F_ProductId
) recv ON recv.F_ProductId = p.F_Id
WHERE p.F_ProductName LIKE '%卸妆水%';
-- 三、卸妆水入库明细（按批次）
SELECT
p.F_ProductName AS 产品名称,
i.F_Id AS 库存记录ID,
i.F_BatchNumber AS 批次号,
i.F_Quantity AS 入库数量,
i.F_StockInTime AS 入库时间,
i.F_StockInType AS 入库类型,
i.F_IsEffective AS 是否有效
FROM lq_inventory i
INNER JOIN lq_product p ON p.F_Id = i.F_ProductId
WHERE p.F_ProductName LIKE '%卸妆水%'
AND i.F_IsEffective = 1
ORDER BY i.F_StockInTime DESC;
-- 四、卸妆水领用明细（仅已领取）
SELECT
p.F_ProductName AS 产品名称,
u.F_UsageBatchId AS 批次ID,
u.F_UsageQuantity AS 领用数量,
u.F_UsageTime AS 使用时间,
a.F_ReceiveTime AS 领取时间,
a.F_ApprovalStatus AS 审批状态,
a.F_IsReceived AS 是否已领取
FROM lq_inventory_usage u
INNER JOIN lq_product p ON p.F_Id = u.F_ProductId
INNER JOIN lq_inventory_usage_application a
ON a.F_UsageBatchId = u.F_UsageBatchId
AND a.F_IsEffective = 1
AND a.F_IsReceived = 1
WHERE p.F_ProductName LIKE '%卸妆水%'
AND u.F_IsEffective = 1
ORDER BY u.F_UsageTime DESC;
-- 五、数据平衡校验（简化版）
-- 期望：入库总量 = 当前可用库存 + 已领取数量
SELECT
p.F_ProductName AS 产品名称,
IFNULL(inv.入库总量, 0) AS 入库总量,
IFNULL(recv.已领取数量, 0) AS 已领取数量,
IFNULL(inv.入库总量, 0) - IFNULL(recv.已领取数量, 0) AS 当前可用库存_计算值,
IFNULL(inv.入库总量, 0) - (IFNULL(inv.入库总量, 0) - IFNULL(recv.已领取数量, 0)) - IFNULL(recv.已领取数量, 0) AS 平衡差_应为0
FROM lq_product p
LEFT JOIN (
SELECT F_ProductId, SUM(F_Quantity) AS 入库总量
FROM lq_inventory WHERE F_IsEffective = 1 GROUP BY F_ProductId
) inv ON inv.F_ProductId = p.F_Id
LEFT JOIN (
SELECT u.F_ProductId, SUM(u.F_UsageQuantity) AS 已领取数量
FROM lq_inventory_usage u
INNER JOIN lq_inventory_usage_application a
ON a.F_UsageBatchId = u.F_UsageBatchId AND a.F_IsEffective = 1 AND a.F_IsReceived = 1
WHERE u.F_IsEffective = 1
GROUP BY u.F_ProductId
) recv ON recv.F_ProductId = p.F_Id
WHERE p.F_ProductName LIKE '%卸妆水%';