如何编写MySQL查询实现基于FIFO方法的库存报表？

需求说明 #

针对persediaan_source库存交易表，实现**先进先出（FIFO）**规则的库存报表，需拆分：

• 采购（id_jenis_transaksi=1）、领用（id_jenis_transaksi=2）的明细
• 不同采购价格对应的库存结余数量明细（bal_qty_detail）
• 领用对应的各批次采购数量明细（use_qty_detail）

表结构与示例数据 #

CREATE TABLE `persediaan_source` (
  `id` int(11) NOT NULL,
  `id_barang` int(11) NOT NULL,
  `jumlah` double NOT NULL,
  `harga` double NOT NULL,
  `tanggal` datetime NOT NULL,
  `id_jenis_transaksi` tinyint(4) NOT NULL COMMENT 'id = 1 -> 采购, id = 2 -> 领用'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `persediaan_source` (`id`, `id_barang`, `jumlah`, `harga`, `tanggal`, `id_jenis_transaksi`) VALUES
(89, 26, 12, 1050000, '2022-07-15 05:55:23', 1),
(90, 26, 8, 0, '2022-07-15 05:55:52', 2),
(91, 26, 16, 1100000, '2022-07-15 05:56:22', 1),
(95, 26, 10, 0, '2022-07-15 05:59:09', 2);

FIFO报表查询SQL #

WITH sorted_transactions AS (
    -- 按商品ID和交易时间排序所有记录，生成全局行号
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY id_barang ORDER BY tanggal) AS row_num
    FROM persediaan_source
),
purchase_batches AS (
    -- 提取采购批次，计算累计采购量
    SELECT 
        id_barang,
        id AS purchase_id,
        jumlah AS purchase_qty,
        harga,
        tanggal,
        SUM(jumlah) OVER(PARTITION BY id_barang ORDER BY tanggal) AS cumulative_purchase
    FROM sorted_transactions
    WHERE id_jenis_transaksi = 1
),
usage_with_cumulative AS (
    -- 提取领用记录，计算累计领用量
    SELECT 
        id_barang,
        id AS usage_id,
        jumlah AS usage_qty,
        tanggal,
        SUM(jumlah) OVER(PARTITION BY id_barang ORDER BY tanggal) AS cumulative_usage
    FROM sorted_transactions
    WHERE id_jenis_transaksi = 2
),
fifo_usage_details AS (
    -- 匹配领用与对应的采购批次，拆分领用明细
    SELECT 
        u.id_barang,
        u.usage_id,
        p.purchase_id,
        p.harga,
        -- 计算该批次被领用的数量：取累计领用与采购批次的交集
        LEAST(p.cumulative_purchase, u.cumulative_usage) - 
        GREATEST(COALESCE(LAG(p.cumulative_purchase) OVER(PARTITION BY u.id_barang, u.usage_id ORDER BY p.tanggal), 0), 
                 COALESCE(LAG(u.cumulative_usage) OVER(PARTITION BY u.id_barang ORDER BY u.tanggal), 0)) AS use_qty_detail
    FROM usage_with_cumulative u
    JOIN purchase_batches p 
        ON p.id_barang = u.id_barang
        AND p.cumulative_purchase > COALESCE(LAG(u.cumulative_usage) OVER(PARTITION BY u.id_barang ORDER BY u.tanggal), 0)
        AND u.cumulative_usage > COALESCE(LAG(p.cumulative_purchase) OVER(PARTITION BY u.id_barang ORDER BY p.tanggal), 0)
),
inventory_balance AS (
    -- 计算每个交易后的库存结余明细
    SELECT 
        st.id_barang,
        st.id,
        st.tanggal,
        st.id_jenis_transaksi,
        st.jumlah,
        st.harga,
        -- 累计采购量减去累计领用量得到当前总结余
        COALESCE((SELECT SUM(jumlah) FROM sorted_transactions WHERE id_barang = st.id_barang AND tanggal <= st.tanggal AND id_jenis_transaksi=1), 0) -
        COALESCE((SELECT SUM(jumlah) FROM sorted_transactions WHERE id_barang = st.id_barang AND tanggal <= st.tanggal AND id_jenis_transaksi=2), 0) AS total_balance,
        -- 拆分各批次的结余数量
        CASE 
            WHEN st.id_jenis_transaksi = 1 THEN 
                st.jumlah - COALESCE((SELECT SUM(use_qty_detail) FROM fifo_usage_details WHERE purchase_id = st.id), 0)
            ELSE 
                (SELECT SUM(purchase_qty) FROM purchase_batches WHERE id_barang = st.id_barang AND tanggal <= st.tanggal) -
                (SELECT SUM(jumlah) FROM sorted_transactions WHERE id_barang = st.id_barang AND tanggal <= st.tanggal AND id_jenis_transaksi=2) -
                COALESCE((SELECT SUM(purchase_qty) FROM purchase_batches WHERE id_barang = st.id_barang AND tanggal < st.tanggal), 0)
        END AS bal_qty_detail
    FROM sorted_transactions st
)
-- 合并最终报表数据，关联领用明细
SELECT 
    ib.id_barang,
    ib.id AS transaction_id,
    CASE ib.id_jenis_transaksi 
        WHEN 1 THEN '采购' 
        WHEN 2 THEN '领用' 
    END AS transaction_type,
    ib.jumlah,
    ib.harga,
    ib.tanggal,
    ib.total_balance,
    ib.bal_qty_detail,
    fud.use_qty_detail
FROM inventory_balance ib
LEFT JOIN fifo_usage_details fud 
    ON ib.id_barang = fud.id_barang 
    AND (ib.id = fud.usage_id OR ib.id = fud.purchase_id)
ORDER BY ib.id_barang, ib.tanggal;

查询结果说明 #

针对示例数据，查询结果将呈现：

• 第1行（采购12件）：bal_qty_detail=12，总结余12
• 第2行（领用8件）：use_qty_detail=8（消耗第一批采购的8件），总结余4
• 第3行（采购16件）：会拆分出两条明细，分别对应第一批剩余的bal_qty_detail=4和新采购批次的bal_qty_detail=16，总结余20
• 第4行（领用10件）：会拆分出两条明细，分别对应第一批剩余的use_qty_detail=4和第二批采购的use_qty_detail=6，总结余10

该查询通过CTE分步处理交易排序、采购批次累计、领用匹配、结余计算，完整实现FIFO规则下的明细拆分。

内容的提问来源于stack exchange，提问作者Dodo Ananto