如何筛选无截止日后批次且到期日早于截止日的PO-客户组合

先拆解下你原查询的两个核心问题：

• 字段引用错误：你写的BATCH.Expiry_Date是不存在的，这个字段属于HEADER表，得改成HEADER.Expiry_Date。
• 逻辑偏差：原查询只是筛选出了BATCH表中日期在截止日前的记录，但如果某个(PO_Number,CUSTNMBR)组合同时存在截止日前和截止日后的批次，原查询会把该组合的截止日前批次数据返回——这完全不符合你“无截止日后批次”的要求。

下面给你三种可靠的实现方案，你可以根据自己的数据库环境选择最顺手的：

方案1：使用NOT EXISTS（最直观，性能通常不错） #

这种方式直接检查当前(PO_Number,CUSTNMBR)在BATCH表中不存在任何录入日期晚于截止日的批次：

SELECT 
    HEADER.PO_Number, 
    HEADER.CUSTNMBR,
    HEADER.Expiry_Date, 
    HEADER.PO_Status, 
    HEADER.STATUSDATE
FROM HEADER
WHERE 
    HEADER.Expiry_Date < '2016-07-12 00:00:00'
    AND NOT EXISTS (
        SELECT 1 
        FROM BATCH
        WHERE 
            BATCH.PORDNMBR = HEADER.PO_Number
            AND BATCH.CUSTNMBR = HEADER.CUSTNMBR
            AND BATCH.DATE1 > '2016-07-12 00:00:00'
    );

如果需要同时返回该PO对应的所有批次信息，可以调整为：

SELECT 
    B.PORDNMBR, 
    B.CUSTNMBR, 
    B.BTCHID, 
    B.DATE1,
    H.PO_Number, 
    H.CUSTNMBR,
    H.Expiry_Date, 
    H.PO_Status, 
    H.STATUSDATE
FROM HEADER H
INNER JOIN BATCH B 
    ON H.PO_Number = B.PORDNMBR 
    AND H.CUSTNMBR = B.CUSTNMBR
WHERE 
    H.Expiry_Date < '2016-07-12 00:00:00'
    AND NOT EXISTS (
        SELECT 1 
        FROM BATCH B2
        WHERE 
            B2.PORDNMBR = H.PO_Number
            AND B2.CUSTNMBR = H.CUSTNMBR
            AND B2.DATE1 > '2016-07-12 00:00:00'
    );

方案2：使用LEFT JOIN + IS NULL #

通过左连接匹配截止日后的批次，筛选那些没有匹配结果的PO（即无截止日后批次）：

SELECT 
    H.PO_Number, 
    H.CUSTNMBR,
    H.Expiry_Date, 
    H.PO_Status, 
    H.STATUSDATE
FROM HEADER H
LEFT JOIN BATCH B 
    ON H.PO_Number = B.PORDNMBR 
    AND H.CUSTNMBR = B.CUSTNMBR
    AND B.DATE1 > '2016-07-12 00:00:00'
WHERE 
    H.Expiry_Date < '2016-07-12 00:00:00'
    AND B.BTCHID IS NULL;

方案3：使用GROUP BY + 聚合函数 #

统计每个PO的最晚批次录入日期，确保最晚日期不超过截止日：

SELECT 
    H.PO_Number, 
    H.CUSTNMBR,
    H.Expiry_Date, 
    H.PO_Status, 
    H.STATUSDATE
FROM HEADER H
INNER JOIN (
    SELECT 
        PORDNMBR, 
        CUSTNMBR,
        MAX(DATE1) AS Latest_Batch_Date
    FROM BATCH
    GROUP BY PORDNMBR, CUSTNMBR
) B 
    ON H.PO_Number = B.PORDNMBR 
    AND H.CUSTNMBR = B.CUSTNMBR
WHERE 
    H.Expiry_Date < '2016-07-12 00:00:00'
    AND B.Latest_Batch_Date <= '2016-07-12 00:00:00';

注意：如果存在HEADER中没有对应BATCH记录的情况，这个方案会漏掉这些PO——如果需要包含这类PO，可以把INNER JOIN改成LEFT JOIN，再加上B.Latest_Batch_Date IS NULL的条件。

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