Epicor10 BAQ需求：按零件号返回最新收货日期单条记录

我完全懂你现在的困扰——刚接触SQL，照着过往问答改还是没搞定，原查询出来一堆重复的非最新记录，太闹心了。咱们一步步来调整你的BAQ语句，解决这个问题。

先说说原查询的核心问题 #

1. cross join Erp.RcvHead是大坑：它会把每一条PODetail和每一条RcvHead强制匹配，直接导致海量重复数据，而且完全没关联收货单和采购单的对应关系。
2. 没有筛选最新收货日期的逻辑：原查询只是把所有关联数据拉出来，没做“每个零件只留最新收货记录”的过滤。

解决方案一：用窗口函数（推荐，逻辑清晰） #

这个方法利用ROW_NUMBER()窗口函数给每个零件的收货记录排序，只保留最新的那一条：

SELECT 
    [Part].[PartNum] AS [Part #],
    [Part].[PartDescription] AS [Part Description],
    [PODetail].[PUM] AS [Supplier UOM],
    [PODetail].[DocUnitCost] AS [Unit Price],
    [RcvHead].[ReceiptDate] AS [Receipt Date]
FROM 
    Erp.Part AS Part
INNER JOIN 
    Erp.PODetail AS PODetail ON Part.Company = PODetail.Company AND Part.PartNum = PODetail.PartNum
-- 新增RcvDetail关联，正确连接PO行和收货行
INNER JOIN 
    Erp.RcvDetail AS RcvDetail ON PODetail.Company = RcvDetail.Company 
        AND PODetail.PONum = RcvDetail.PONum 
        AND PODetail.POLine = RcvDetail.POLine
INNER JOIN 
    Erp.RcvHead AS RcvHead ON RcvDetail.Company = RcvHead.Company AND RcvDetail.RcvNum = RcvHead.RcvNum
-- 子查询：给每个零件的收货记录排序，取最新的那条
INNER JOIN (
    SELECT 
        PartNum,
        ReceiptDate,
        -- 按零件分组，收货日期倒序排，最新的记录排第1
        ROW_NUMBER() OVER (PARTITION BY PartNum ORDER BY ReceiptDate DESC) AS RowRank
    FROM (
        -- 先去重，避免同一零件同一日期的重复记录干扰排序
        SELECT DISTINCT 
            P.PartNum,
            RH.ReceiptDate
        FROM Erp.PODetail P
        INNER JOIN Erp.RcvDetail RD ON P.Company = RD.Company AND P.PONum = RD.PONum AND P.POLine = RD.POLine
        INNER JOIN Erp.RcvHead RH ON RD.Company = RH.Company AND RD.RcvNum = RH.RcvNum
    ) AS PartReceipts
) AS LatestReceipt ON Part.PartNum = LatestReceipt.PartNum 
    AND RcvHead.ReceiptDate = LatestReceipt.ReceiptDate 
    AND LatestReceipt.RowRank = 1

解决方案二：用子查询找最大收货日期（适合新手理解） #

如果对窗口函数不太熟悉，可以先找出每个零件的最新收货日期，再关联回主查询：

SELECT 
    [Part].[PartNum] AS [Part #],
    [Part].[PartDescription] AS [Part Description],
    [PODetail].[PUM] AS [Supplier UOM],
    [PODetail].[DocUnitCost] AS [Unit Price],
    [RcvHead].[ReceiptDate] AS [Receipt Date]
FROM 
    Erp.Part AS Part
INNER JOIN 
    Erp.PODetail AS PODetail ON Part.Company = PODetail.Company AND Part.PartNum = PODetail.PartNum
INNER JOIN 
    Erp.RcvDetail AS RcvDetail ON PODetail.Company = RcvDetail.Company 
        AND PODetail.PONum = RcvDetail.PONum 
        AND PODetail.POLine = RcvDetail.POLine
INNER JOIN 
    Erp.RcvHead AS RcvHead ON RcvDetail.Company = RcvHead.Company AND RcvDetail.RcvNum = RcvHead.RcvNum
-- 子查询：先算出每个零件的最新收货日期
INNER JOIN (
    SELECT 
        P.PartNum,
        MAX(RH.ReceiptDate) AS LatestReceiptDate
    FROM Erp.PODetail P
    INNER JOIN Erp.RcvDetail RD ON P.Company = RD.Company AND P.PONum = RD.PONum AND P.POLine = RD.POLine
    INNER JOIN Erp.RcvHead RH ON RD.Company = RH.Company AND RD.RcvNum = RH.RcvNum
    GROUP BY P.PartNum
) AS MaxReceipt ON Part.PartNum = MaxReceipt.PartNum 
    AND RcvHead.ReceiptDate = MaxReceipt.LatestReceiptDate

额外提醒 #

• 如果同一个零件在同一天有多个收货记录，以上两种方法会返回所有当天的记录。要是只想留一条，可以在排序时加个额外字段，比如窗口函数里改成ORDER BY ReceiptDate DESC, RcvNum DESC，优先取最新的收货单号。
• 一定要保留Company字段的关联，Epicor是多公司架构，漏了可能会跨公司取到错误数据。

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