主页 / dba / 问题 / 154592
Accepted
Mei_R
Asked: 2016-11-09 05:14:01 +0800 CST

根据先进先出 (FIFO) 计算数量

  • 772

我正在尝试根据 FIFO 获得数量结果，下面有 2 个表格：

表购买：

| PO    | Date         | Quantity | Item | 
|-------|--------------|----------|------|
| PO001 | 01-Jan-2016  | 3        | AO21 |  
| PO002 | 10-Jan-2016  | 7        | AO21 |  
| PO003 | 01-Feb-2016  | 3        | AO21 |

表库存：

| SO    | Date        | Quantity | Item |
|-------|-------------|----------|------|
| SO001 | 02-Jan-2016 | 2        | AO21 |
| SO002 | 11-Feb-2016 | 8        | AO21 |
| SO003 | 12-Feb-2016 | 6        | AO23 |

我希望输出是这样的：

| SO    | PO    | Quantity |
|-------|-------|----------|
| SO001 | PO001 | 2        |
| SO002 | PO001 | 1        |
| SO002 | PO003 | 7        |

您对查看此输出的查询有任何想法吗？从行中计算的 SO 和 PO 的结果。更多解释：

想要的结果中的 2、1、7 来自哪里？

从stock和purchase。item 的第一个（按日期）库存值为A0212，而第一次购买 ( PO001) 需要 3，所以 stock sold 2 并且我们在结果中得到这一行：

| SO001 | PO001 | 2        |

我们还需要购买 1 个，然后下一个库存值为 8。所以这次购买完成，我们得到 1 个（还有 7 个库存）：

| SO002 | PO001 | 1        |

下一次购买 ( PO002) 需要 7 个，而我们正好剩下 7 个，因此购买完成（该商品还剩下 0 个库存）。我们得到：

| SO002 | PO003 | 7        |

购买PO003需要 3，但没有剩余库存，因此我们在该购买的结果中没有任何行。

mysql running-totals

1 个回答

  1. Best Answer

    这不是一个微不足道的问题，但使用窗口函数（以及 CTE 以提高可读性）不会很难。

    MySQL 也没有实现，但让我们看看它是如何实现的：

    WITH 
  running_purchase AS
  ( SELECT po, date, quantity, item,
           SUM(quantity) OVER (PARTITION BY item
                               ORDER BY date, po
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW)
             AS running_total
    FROM purchase
  ),
  running_stock AS
  ( SELECT so, date, quantity, item,
           SUM(quantity) OVER (PARTITION BY item
                               ORDER BY date, so
                               ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW)
             AS running_total
    FROM stock
  )
SELECT 
    s.so, p.po, p.item,
    LEAST(p.running_total, s.running_total) 
    - GREATEST(s.running_total - s.quantity, p.running_total - p.quantity)
        AS quantity
FROM running_purchase AS p
  JOIN running_stock AS s
    ON  p.item = s.item
    AND s.running_total - s.quantity < p.running_total  
    AND p.running_total - p.quantity < s.running_total 
ORDER BY
    p.item, p.date, p.po ;

    在SQLFiddle测试（在 Postgres 中）。

    请注意，MariaDB（可以替代 MySQL）已经宣布他们正在研究窗口函数和 CTE，可能在他们的下一个版本（10.2）上。请参阅MariaDB 10.2 发行说明。

    对于当前的 MySQL 版本，它必须更复杂，但逻辑是相同的：

    SELECT 
    s.so, p.po, p.item,
    LEAST(p.running_total, s.running_total) 
    - GREATEST(s.running_total - s.quantity, p.running_total - p.quantity)
        AS quantity
FROM 
     ( SELECT p1.po, p1.date, p1.quantity, p1.item,
             SUM(p2.quantity) AS running_total
       FROM purchase AS p1
         JOIN purchase AS p2
           ON  p1.item = p2.item
           AND  ( p1.date > p2.date
               OR p1.date = p2.date AND p1.po >= p2.po)
       GROUP BY p1.item, p1.date, p1.po
     ) AS p
  JOIN
     ( SELECT s1.so, s1.date, s1.quantity, s1.item,
             SUM(s2.quantity) AS running_total
       FROM stock AS s1
         JOIN stock AS s2
           ON  s1.item = s2.item
           AND  ( s1.date > s2.date
               OR s1.date = s2.date AND s1.so >= s2.so)
       GROUP BY s1.item, s1.date, s1.so
     ) AS s
  ON  p.item = s.item
  AND s.running_total - s.quantity < p.running_total  
  AND p.running_total - p.quantity < s.running_total 
ORDER BY 
    p.item, p.date, p.po ;

    在SQLFiddle-2（在 MySQL 5.6 中）测试。

    • 6

相关问题