MySQL关联最新记录计算指定日期库存总值问题求助

我明白你的问题所在了——你的原语句会把某个物料所有截止日期前的库存历史记录都累加，但实际上我们只需要每个物料在该日期前的最后一条库存余额记录来计算总值。下面给你两种可行的解决方案，分别适配不同版本的MySQL：

方案一：使用窗口函数（MySQL 8.0+ 推荐） #

窗口函数是MySQL 8.0及以上版本的特性，能非常简洁地筛选出每个物料的最新库存记录：

SELECT 
    SUM(s.cost_per_unit * COALESCE(h.quantity_balance, s.qty)) AS total_value
FROM tbl_stock s
LEFT JOIN (
    -- 子查询：获取每个物料截止到2018-03-31的最新库存历史记录
    SELECT 
        part_ID,
        quantity_balance
    FROM (
        SELECT 
            part_ID,
            quantity_balance,
            -- 按物料分组，按记录日期倒序编号，最新记录编号为1
            ROW_NUMBER() OVER (PARTITION BY part_ID ORDER BY date_of_entry DESC) AS rn
        FROM tbl_stock_history
        WHERE date_of_entry <= '2018-03-31'
    ) AS latest_stock
    WHERE rn = 1 -- 只保留每个物料的最新记录
) h ON s.part_ID = h.part_ID
WHERE s.department = 1 
  AND s.qty > 0;

关键细节说明： #

• ROW_NUMBER() OVER (PARTITION BY part_ID ORDER BY date_of_entry DESC)：按物料ID分组，每组内按记录日期从新到旧排序，给每条记录分配唯一编号，最新的记录编号为1。
• COALESCE(h.quantity_balance, s.qty)：处理物料没有历史记录的情况（比如刚入库还没产生操作记录），此时用库存表中的qty作为库存余额。

方案二：兼容MySQL 5.x版本（无窗口函数） #

如果你使用的是MySQL 5.x版本，不支持窗口函数，可以用关联子查询来实现：

SELECT 
    SUM(s.cost_per_unit * COALESCE(h.quantity_balance, s.qty)) AS total_value
FROM tbl_stock s
LEFT JOIN (
    -- 子查询：先找到每个物料截止到指定日期的最新记录日期
    SELECT 
        sh.part_ID,
        sh.quantity_balance
    FROM tbl_stock_history sh
    INNER JOIN (
        SELECT part_ID, MAX(date_of_entry) AS max_date
        FROM tbl_stock_history
        WHERE date_of_entry <= '2018-03-31'
        GROUP BY part_ID
    ) sh_max ON sh.part_ID = sh_max.part_ID AND sh.date_of_entry = sh_max.max_date
) h ON s.part_ID = h.part_ID
WHERE s.department = 1 
  AND s.qty > 0;

关键细节说明： #

• 内层子查询sh_max先找出每个物料截止到指定日期的最新记录日期。
• 再通过关联tbl_stock_history，拿到该日期对应的库存余额。
• 同样用COALESCE处理无历史记录的边界情况。

额外注意事项 #

1. 确保tbl_stock_history中的quantity_balance是截止到该日期的累计库存余额，而非单次操作的变动量，否则计算结果会出错。
2. 如果同一物料在同一天有多条历史记录，上述方案会取其中一条（窗口函数方案取排序后的第一条，关联子查询方案取任意一条）。如果需要合并同一天的记录，可在子查询中对quantity_balance做聚合（比如SUM），具体取决于你的业务逻辑。

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