如何实现工厂间库存往返调拨的识别与统计报表生成？

云上老周

2026-8-3

库存退回识别与统计方案

数据库示例

日期（Date）	零件编号（Part No.）	始发工厂（Origin）	目的工厂（Destination）	成本（Cost）	数量（Quantity）
1/29/2023	100	MIA	MCO	$500.00	500
1/29/2023	100	MIA	ATL	$450.00	500
1/30/2023	100	JFK	MIA	$700.00	500
1/30/2023	100	MCO	SFB	$700.00	500

核心逻辑与实现

要识别库存从始发工厂发出后是否被退回，关键是匹配同一零件下的双向移动记录：即存在记录A（始发X→目的Y）和记录B（始发Y→目的X）。基于此可计算所需统计指标：

SQL查询实现

WITH plant_movements AS (
    SELECT Origin, Destination, Quantity FROM stock_movements
),
bidirectional_plants AS (
    -- 筛选存在双向移动的工厂
    SELECT DISTINCT m1.Origin AS plant
    FROM plant_movements m1
    JOIN plant_movements m2 
    ON m1.Origin = m2.Destination AND m1.Destination = m2.Origin
    UNION
    SELECT DISTINCT m1.Destination AS plant
    FROM plant_movements m1
    JOIN plant_movements m2 
    ON m1.Origin = m2.Destination AND m1.Destination = m2.Origin
)
-- 生成统计汇总
SELECT
    (SELECT COUNT(*) FROM bidirectional_plants) AS "同时有入站和出站记录的工厂数量",
    (SELECT SUM(Quantity) FROM stock_movements 
     WHERE Origin IN (SELECT plant FROM bidirectional_plants) 
        OR Destination IN (SELECT plant FROM bidirectional_plants)) AS "出入站总数量",
    CONCAT(ROUND(
        (SELECT SUM(Quantity) FROM stock_movements 
         WHERE Origin IN (SELECT plant FROM bidirectional_plants) 
            OR Destination IN (SELECT plant FROM bidirectional_plants))
        / (SELECT SUM(Quantity) FROM stock_movements) * 100, 0), '%') AS "出入站数量占总库存移动量的比例"
FROM dual;