SQL实现：通过外层引用限制内层分组排名，解决库存收发成本匹配的笛卡尔积问题

基础核算规则 #

• 基于M1 ERP系统不可变数据库结构，开展作业维度的物料收发交易成本核算
• 同作业下的同物料支持多批次不同成本收货，收货来源分为两类：Mfg Receipt、Receipt Lines
• 两类收货记录均无匹配时，成本默认取Cost Type表中可靠存储的平均库存成本
• 发料、退料交易自带的Cost Type字段存储的收货成本不可靠，必须回溯对应交易发生前的收货记录计算成本，取值逻辑通过COALESCE实现优先级兜底

核心匹配要求 #

• 退料冲销回库存时，需严格按照时间顺序，为每笔退料匹配同作业、同装配、同物料维度下、早于当前退料交易的最近一次收货成本
• 严格约束匹配边界：仅可匹配早于当前退料交易的收货记录，且仅取距离退料时间最近的1笔，不得匹配时间过早的非最近收货记录、也不得匹配晚于退料交易时间的收货记录
• 规避多对多连接的笛卡尔积风险：测试场景中物料21的简单连接逻辑会产生3笔收货对3笔退料的9行交叉结果，最终净收发数量为0但净成本计算存在偏差

现有方案缺陷 #

• 已尝试通过CTE结合ROW_NUMBER()窗口排名实现最近收货成本匹配，但无法在窗口排名计算时引入外层发料交易的imtPartTransactionID作为过滤条件
• 上述缺陷直接导致排名分区范围过宽、内层连接的时间约束逻辑存在漏洞，无法正确为每笔退料匹配唯一的最近收货成本

可行实现方案 #

• 替换“全局CTE预计算排名再连接”的旧模式，采用逐笔退料交易的关联子查询逻辑，解决窗口函数无法引用外层交易字段的限制
• 具体匹配流程：对每笔退料交易，先过滤出同作业、同装配、同物料、交易时间早于当前退料的收货候选集，再对该候选集按收货交易时间倒序排序，取排名第1的记录对应成本作为当前退料的核算成本
• 成本取值优先级通过COALESCE按顺序拼接：退料关联的最近收货成本 → Mfg Receipt匹配成本 → Receipt Lines匹配成本 → Cost Type表存储的可靠平均库存成本
• 关联关系严格控制为1对1（每笔退料仅关联1条收货成本记录），从根源上避免多对多连接产生的多行交叉、成本失真问题

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