SQL多表内连接去重：按物料ID留单条记录并取最早过期日期

需要编写一个涉及3张表的内连接SQL查询，要求每个MATERIAL_ID（物料ID）仅显示一条结果。已编写的查询如下：

SELECT DISTINCT
A.ORDER_ID, A.BATCH_ID, A.MATERIAL_ID, B.MATERIAL_DESC, A.TARGET_QTY, A.DISPENSED_QTY,     A.REMAINING_QTY, A.DISPENSED_UOM, A.DISPENSE_STATUS, B.MATERIAL_TYPE, A.UNIT_PROCEDURE_ID, A.SPLIT_ID, A.BOM_REF_NO, C.CONTAINER_STATUS, C.AREA_ID, C.QTY_STATUS, C.EXPIRE_DATE 
FROM
MM_DISP_MATL_ST A
INNER JOIN 
MM_MATERIAL_SP B
ON A.MATERIAL_ID = B.MATERIAL_ID
INNER JOIN
MM_CONTAINER_ST C
ON B.MATERIAL_ID = C.MATERIAL_ID
AND C.CONTAINER_STATUS = 'Unrestricted' 
AND C.QTY_STATUS IN ('Full','Partial')
WHERE A.ORDER_ID = :pORDER_NUMBER
ORDER BY A.BOM_REF_NO

该查询返回的结果中MATERIAL_ID、AREA_ID和EXPIRE_DATE存在重复，尝试使用DISTINCT和额外ORDER BY均未解决问题，需要修改查询实现按MATERIAL_ID或BOM_REF_NO仅显示一条结果，并选取最早的EXPIRE_DATE。

核心思路是先从MM_CONTAINER_ST表中筛选出每个MATERIAL_ID对应最早EXPIRE_DATE的记录，再与另外两张表关联，避免因容器表多条记录导致的重复。

方法一：使用窗口函数（推荐，逻辑清晰且高效） #

通过ROW_NUMBER()窗口函数对每个MATERIAL_ID的容器记录按EXPIRE_DATE升序排序，取排序为1的那条（即最早过期的记录）：

SELECT 
    A.ORDER_ID, A.BATCH_ID, A.MATERIAL_ID, B.MATERIAL_DESC, 
    A.TARGET_QTY, A.DISPENSED_QTY, A.REMAINING_QTY, A.DISPENSED_UOM, 
    A.DISPENSE_STATUS, B.MATERIAL_TYPE, A.UNIT_PROCEDURE_ID, A.SPLIT_ID, 
    A.BOM_REF_NO, C.CONTAINER_STATUS, C.AREA_ID, C.QTY_STATUS, C.EXPIRE_DATE 
FROM
    MM_DISP_MATL_ST A
INNER JOIN 
    MM_MATERIAL_SP B ON A.MATERIAL_ID = B.MATERIAL_ID
INNER JOIN (
    SELECT 
        MATERIAL_ID, CONTAINER_STATUS, AREA_ID, QTY_STATUS, EXPIRE_DATE,
        ROW_NUMBER() OVER (PARTITION BY MATERIAL_ID ORDER BY EXPIRE_DATE ASC) AS rn
    FROM MM_CONTAINER_ST
    WHERE CONTAINER_STATUS = 'Unrestricted' 
      AND QTY_STATUS IN ('Full','Partial')
) C ON B.MATERIAL_ID = C.MATERIAL_ID AND C.rn = 1
WHERE A.ORDER_ID = :pORDER_NUMBER
ORDER BY A.BOM_REF_NO

方法二：使用关联子查询获取最早过期日期 #

如果数据库不支持窗口函数，可通过子查询先获取每个MATERIAL_ID的最小EXPIRE_DATE，再关联容器表拿到对应记录：

SELECT 
    A.ORDER_ID, A.BATCH_ID, A.MATERIAL_ID, B.MATERIAL_DESC, 
    A.TARGET_QTY, A.DISPENSED_QTY, A.REMAINING_QTY, A.DISPENSED_UOM, 
    A.DISPENSE_STATUS, B.MATERIAL_TYPE, A.UNIT_PROCEDURE_ID, A.SPLIT_ID, 
    A.BOM_REF_NO, C.CONTAINER_STATUS, C.AREA_ID, C.QTY_STATUS, C.EXPIRE_DATE 
FROM
    MM_DISP_MATL_ST A
INNER JOIN 
    MM_MATERIAL_SP B ON A.MATERIAL_ID = B.MATERIAL_ID
INNER JOIN MM_CONTAINER_ST C 
    ON B.MATERIAL_ID = C.MATERIAL_ID
    AND C.CONTAINER_STATUS = 'Unrestricted' 
    AND C.QTY_STATUS IN ('Full','Partial')
    AND C.EXPIRE_DATE = (
        SELECT MIN(EXPIRE_DATE) 
        FROM MM_CONTAINER_ST 
        WHERE MATERIAL_ID = C.MATERIAL_ID
          AND CONTAINER_STATUS = 'Unrestricted' 
          AND QTY_STATUS IN ('Full','Partial')
    )
WHERE A.ORDER_ID = :pORDER_NUMBER
GROUP BY A.MATERIAL_ID, A.ORDER_ID, A.BATCH_ID, B.MATERIAL_DESC, 
         A.TARGET_QTY, A.DISPENSED_QTY, A.REMAINING_QTY, A.DISPENSED_UOM, 
         A.DISPENSE_STATUS, B.MATERIAL_TYPE, A.UNIT_PROCEDURE_ID, A.SPLIT_ID, 
         A.BOM_REF_NO, C.CONTAINER_STATUS, C.AREA_ID, C.QTY_STATUS, C.EXPIRE_DATE
ORDER BY A.BOM_REF_NO

关键说明 #

• 原查询DISTINCT无效的原因：AREA_ID和EXPIRE_DATE不同会导致整行记录被判定为不重复，因此必须先过滤容器表的重复记录再关联；
• 若同一MATERIAL_ID存在多条相同最早EXPIRE_DATE的容器记录，窗口函数方法会随机取一条，如需指定规则（比如优先取某AREA_ID），可在ORDER BY后追加条件，如EXPIRE_DATE ASC, AREA_ID。

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