如何基于FIFO过期日期从库存表获取准确批次ID

我来帮你搞定这个先进先出（FIFO）批次查询的问题！你的现有查询返回错误批次ID的原因很明确：没有把Batch_ID和最小剩余天数正确关联起来，数据库只能随机返回一个符合过滤条件的批次，而不是对应最先过期的那个。

问题分析 #

你的需求是从库存表中获取：

• 产品ID为148、仓库ID为1
• 库存数量>0
• **最先过期（剩余天数最少）**的批次ID和剩余天数

原查询直接将Batch_ID与聚合函数MIN(datediff(...))并列查询，但没有对Batch_ID做分组或关联逻辑，导致数据库无法匹配到正确的批次。

解决方案 #

这里提供两种可靠的实现方式，适配不同的数据库版本：

方法1：子查询匹配最小剩余天数

这种方法兼容性强，几乎适用于所有数据库：

SELECT 
    Batch_ID,
    DATEDIFF(Expiry_Date, NOW()) AS Remaining_Days
FROM Stock
WHERE 
    Product_ID = '148' 
    AND Quantity > 0 
    AND Location_ID = '1'
    AND DATEDIFF(Expiry_Date, NOW()) = (
        -- 先找到符合条件的批次中最小的剩余天数
        SELECT MIN(DATEDIFF(Expiry_Date, NOW()))
        FROM Stock
        WHERE Product_ID = '148' 
            AND Quantity > 0 
            AND Location_ID = '1'
    );

逻辑说明：

1. 子查询先计算出所有符合条件批次的最小剩余天数
2. 外层查询筛选出剩余天数等于这个最小值的批次，从而得到对应的Batch_ID

方法2：窗口函数排序（适用于MySQL 8.0+、PostgreSQL等）

如果你的数据库支持窗口函数，这种方法更灵活，还能处理多批次同天过期的场景：

SELECT Batch_ID, Remaining_Days
FROM (
    SELECT 
        Batch_ID,
        DATEDIFF(Expiry_Date, NOW()) AS Remaining_Days,
        -- 按剩余天数升序排序，最先过期的批次排第1
        ROW_NUMBER() OVER (ORDER BY DATEDIFF(Expiry_Date, NOW()) ASC) AS rn
    FROM Stock
    WHERE 
        Product_ID = '148' 
        AND Quantity > 0 
        AND Location_ID = '1'
) AS ranked_batches
WHERE rn = 1;

逻辑说明：

1. 内层查询给每个符合条件的批次按剩余天数升序编号
2. 外层查询直接取编号为1的批次，也就是最先过期的那个

测试验证 #

用你提供的库存数据测试，两种方法都会返回：

完全符合你的期望输出。

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