根據先進先出 (FIFO) 計算數量

我正在嘗試根據 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，但沒有剩餘庫存，因此我們在該購買的結果中沒有任何行。

這不是一個微不足道的問題，但使用視窗函式（以及 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 中）測試。

引用自：https://dba.stackexchange.com/questions/154592