分组后如何获取首行数据？FIFO库存查询问题求助

原始表结构与数据 #

需求说明 #

售卖商品时，需按FIFO先进先出规则，展示当前库存中最早入库的批次（LOTNO），期望结果如下：

尝试的SQL语句 #

SELECT ITEMNO, LOCATIONNO, LOTNO, SUM(INCOME-OUTGO) STOCK
FROM LOCATION_DETAIL 
WHERE ITEMNO = 'AAA'
GROUP BY ITEMNO, LOCATIONNO, LOTNO
ORDER BY LOTNO

错误执行结果 #

问题排查与修正 #

原SQL的核心错误 #

1. 分组计算逻辑失效：正常情况下，GROUP BY ITEMNO, LOCATIONNO, LOTNO应将同一批次的出入库记录合并计算，得到A-01/001库存为0（1-1）、A-02/002库存为1。你得到重复行是因为数据库可能开启了宽松GROUP BY模式，导致分组未正确合并记录。
2. 未过滤无库存批次：原SQL没有筛选库存大于0的批次，A-01/001实际库存为0，不应出现在可选列表中。
3. 未实现FIFO筛选逻辑：即使得到正确库存数据，也没有按入库先后顺序筛选出最早的可用批次。

修正后的SQL #

通过CTE先计算各批次实际库存，再筛选有效库存并按入库顺序取最早批次：

WITH batch_stock AS (
    SELECT 
        ITEMNO, 
        LOCATIONNO, 
        LOTNO, 
        SUM(INCOME - OUTGO) AS STOCK,
        MIN(ID) AS earliest_id -- 用ID递增代表入库先后顺序
    FROM LOCATION_DETAIL
    WHERE ITEMNO = 'AAA'
    GROUP BY ITEMNO, LOCATIONNO, LOTNO
    HAVING SUM(INCOME - OUTGO) > 0 -- 仅保留有库存的批次
)
SELECT ITEMNO, LOCATIONNO, LOTNO, STOCK
FROM batch_stock
ORDER BY earliest_id ASC
LIMIT 1;

执行结果说明 #

该SQL会先合并同一批次的出入库数据，过滤掉库存为0的A-01/001批次，再按入库时间（最小ID）选出最早的可用批次A-02/002，完全符合FIFO的需求。

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