如何正确关联表以获取MIN聚合后目标批次的过期日期

看起来你遇到的核心问题是：在原查询中把BM.XPIRE_DATE加入了GROUP BY子句，而每个批次对应唯一的过期日期，这会让数据库把每个(ITEM_ID, CREATE_DATE_TIME, XPIRE_DATE)的组合都当作独立分组，最终导致你得到了所有批次的结果，而不是仅保留每个商品的最小批次。

下面给你两种可靠的解决方案，你可以根据自己的SQL环境选择：

方法1：先通过子查询获取最小批次，再关联表 #

这种方法的思路是：先从PIX_TRAN中筛选出符合条件的记录，分组得到每个商品的最小批次；再用这个结果关联PIX_TRAN和BATCH_MASTER，精准获取目标批次的过期日期。

SELECT 
    TO_CHAR(PT.CREATE_DATE_TIME, 'MM/DD/YY') AS create_date,
    PT.ITEM_ID,
    min_batch.min_batch_nbr,
    BM.XPIRE_DATE
FROM (
    -- 第一步：先获取每个ITEM_ID对应的最小批次号和创建日期
    SELECT 
        ITEM_ID,
        MIN(BATCH_NBR) AS min_batch_nbr,
        CREATE_DATE_TIME
    FROM PIX_TRAN
    WHERE 
        TRAN_TYPE = '605' 
        AND TRAN_CODE = '01' 
        -- 建议用TRUNC替代TO_CHAR来筛选日期，避免索引失效（如果有日期索引的话）
        AND TRUNC(CREATE_DATE_TIME) = DATE '2025-07-16'
    GROUP BY ITEM_ID, CREATE_DATE_TIME
) min_batch
-- 关联回PIX_TRAN获取完整的交易记录（也可以跳过这步直接关联BATCH_MASTER，这里是为了保留创建日期字段）
JOIN PIX_TRAN PT 
    ON PT.ITEM_ID = min_batch.ITEM_ID 
    AND PT.BATCH_NBR = min_batch.min_batch_nbr
    AND PT.TRAN_TYPE = '605' 
    AND PT.TRAN_CODE = '01'
-- 关联BATCH_MASTER获取目标批次的过期日期
JOIN BATCH_MASTER BM 
    ON BM.ITEM_ID = PT.ITEM_ID 
    AND BM.BATCH_NBR = PT.BATCH_NBR
ORDER BY PT.ITEM_ID;

方法2：使用窗口函数筛选最小批次（更直观简洁） #

窗口函数ROW_NUMBER()可以帮我们在每个商品组内对批次号排序，直接标记出最小批次的记录，再关联过期日期表即可：

SELECT 
    TO_CHAR(PT.CREATE_DATE_TIME, 'MM/DD/YY') AS create_date,
    PT.ITEM_ID,
    PT.BATCH_NBR,
    BM.XPIRE_DATE
FROM (
    SELECT 
        CREATE_DATE_TIME,
        ITEM_ID,
        BATCH_NBR,
        -- 按商品分组，批次号升序排序，最小批次的行号标记为1
        ROW_NUMBER() OVER (PARTITION BY ITEM_ID ORDER BY BATCH_NBR ASC) AS rn
    FROM PIX_TRAN
    WHERE 
        TRAN_TYPE = '605' 
        AND TRAN_CODE = '01' 
        AND TRUNC(CREATE_DATE_TIME) = DATE '2025-07-16'
) PT
JOIN BATCH_MASTER BM 
    ON BM.ITEM_ID = PT.ITEM_ID 
    AND BM.BATCH_NBR = PT.BATCH_NBR
WHERE PT.rn = 1  -- 只保留每个商品的最小批次记录
ORDER BY PT.ITEM_ID;

额外优化建议 #

• 避免用TO_CHAR(CREATE_DATE_TIME)来筛选日期：这种格式转换会导致数据库无法使用CREATE_DATE_TIME上的索引，建议用TRUNC(CREATE_DATE_TIME) = DATE '2025-07-16'或者直接比较日期范围（比如CREATE_DATE_TIME BETWEEN TIMESTAMP '2025-07-16 00:00:00' AND TIMESTAMP '2025-07-16 23:59:59'），提升查询效率。
• 如果你的数据库支持FETCH FIRST或者TOP语法，窗口函数的方法会更灵活，还能轻松扩展到获取N个最小批次的场景。

内容来源于stack exchange