SQL查询每个零件PART_ID对应的最新采购订单PO实现方法

符合需求的SQL查询写法 #

写法1：通用子查询写法（兼容所有主流数据库） #

该写法沿用你之前使用的最大日期匹配逻辑，先关联头表和行表，再按零件ID匹配最新的订单日期：

SELECT
    po.ORDER_DATE,
    pol.PURC_ORDER_ID,
    pol.PART_ID,
    pol.UNIT_PRICE,
    pol.USER_ORDER_QTY AS ORDER_QTY
FROM
    PURC_ORDER_LINE pol
INNER JOIN PURCHASE_ORDER po
    ON pol.PURC_ORDER_ID = po.ID
WHERE
    po.ORDER_DATE = (
        SELECT MAX(po2.ORDER_DATE)
        FROM PURC_ORDER_LINE pol2
        INNER JOIN PURCHASE_ORDER po2
            ON pol2.PURC_ORDER_ID = po2.ID
        WHERE pol2.PART_ID = pol.PART_ID
    )

写法2：窗口函数写法（支持MySQL 8.0+/SQL Server/PostgreSQL/Oracle等新版本数据库） #

用ROW_NUMBER()窗口函数按零件分组、按订单日期倒序排序，直接取每组第一条记录即可：

SELECT
    ORDER_DATE,
    PURC_ORDER_ID,
    PART_ID,
    UNIT_PRICE,
    USER_ORDER_QTY AS ORDER_QTY
FROM (
    SELECT
        po.ORDER_DATE,
        pol.PURC_ORDER_ID,
        pol.PART_ID,
        pol.UNIT_PRICE,
        pol.USER_ORDER_QTY,
        ROW_NUMBER() OVER (PARTITION BY pol.PART_ID ORDER BY po.ORDER_DATE DESC) AS rn
    FROM PURC_ORDER_LINE pol
    INNER JOIN PURCHASE_ORDER po
        ON pol.PURC_ORDER_ID = po.ID
) t
WHERE rn = 1

如果同一零件同一天存在多个采购订单，你可以将ROW_NUMBER()替换为RANK()，即可取出同一天的所有最新订单记录

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