Oracle查询：按SUBINVENTORY_CODE取MAX(COST_DATE)，无需多余分组字段

数据老陈

2026-6-22

解决方案：按子库存获取最新成本日期记录

在Oracle中可以用**窗口函数ROW_NUMBER()**实现你的需求，不需要对那些不需要分组的字段做GROUP BY，具体修改后的SQL如下：

SELECT INVENTORY_ITEM_ID,
       SUBINVENTORY_CODE,
       REC_TRXN_ID,
       TRANSACTION_ID,
       COST_DATE,
       QUANTITY_ONHAND,
       TRANSACTION_COST
FROM (
    SELECT A.INVENTORY_ITEM_ID,
           C.SUBINVENTORY_CODE,
           A.REC_TRXN_ID,
           B.TRANSACTION_ID,
           B.COST_DATE,
           A.QUANTITY_ONHAND,
           B.TRANSACTION_COST,
           -- 按子库存分区，每个分区内按成本日期倒序编号，最新的记录行号为1
           ROW_NUMBER() OVER (PARTITION BY C.SUBINVENTORY_CODE ORDER BY B.COST_DATE DESC NULLS LAST) AS RN
    FROM CST_ONHAND_V A,
         CST_ITEM_COST_HISTORY_V B,
         CST_TXN_LAYER_DTLS_V C
    WHERE 1=1
        AND A.INVENTORY_ITEM_ID = :p_inv_num
        AND A.REC_TRXN_ID = B.TRANSACTION_ID
        AND A.COST_ORG_ID = B.COST_ORG_ID
        AND A.COST_BOOK_ID = B.COST_BOOK_ID
        AND A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID                           
        AND A.REC_TRXN_ID = C.REC_TRXN_ID                             
)
WHERE RN = 1;