如何用JPA/MySQL单查询获取指定日期仓库各商品最新库存记录

没问题，完全可以用单条查询实现需求，不管是原生MySQL语句还是JPA的JPQL都能做到，彻底避免循环查询每个商品带来的性能损耗，尤其适合商品数量较多的场景。

原生MySQL查询语句 #

核心思路是先找出目标日期内指定仓库每个商品的最晚交易时间，再关联原表获取对应的完整记录：

SELECT wt.*
FROM warehouse_transaction wt
INNER JOIN (
    -- 子查询：统计2020-02-01当天1号仓库每个商品的最晚交易时间
    SELECT ItemId, MAX(Date) AS latest_date
    FROM warehouse_transaction
    WHERE WarehouseId = 1 
      AND DATE(Date) = '2020-02-01'
    GROUP BY ItemId
) sub ON wt.ItemId = sub.ItemId 
    AND wt.Date = sub.latest_date
    AND wt.WarehouseId = 1
    AND DATE(wt.Date) = '2020-02-01';

这个查询会精准返回2020-02-01当天1号仓库每个商品的最后一条交易记录（注：你的示例中Id3的交易日期是2020-01-01，不在目标日期范围内，可能是笔误，实际返回的是Id6、7对应的记录）。

JPA JPQL查询语句 #

如果用JPA，可以直接写JPQL实现相同逻辑，这里提供两种写法：

写法1：IN子查询方式 #

@Query("SELECT wt FROM WarehouseTransaction wt " +
       "WHERE wt.warehouseId = :warehouseId " +
       "AND FUNCTION('DATE', wt.date) = :targetDate " +
       "AND (wt.itemId, wt.date) IN (" +
           "SELECT sub.itemId, MAX(sub.date) " +
           "FROM WarehouseTransaction sub " +
           "WHERE sub.warehouseId = :warehouseId " +
           "AND FUNCTION('DATE', sub.date) = :targetDate " +
           "GROUP BY sub.itemId" +
       ")")
List<WarehouseTransaction> findLatestDailyStock(
    @Param("warehouseId") Integer warehouseId, 
    @Param("targetDate") String targetDate
);

写法2：JOIN子查询方式 #

和MySQL的逻辑完全对应，可读性更强：

@Query("SELECT wt FROM WarehouseTransaction wt " +
       "JOIN (" +
           "SELECT sub.itemId, MAX(sub.date) AS latestDate " +
           "FROM WarehouseTransaction sub " +
           "WHERE sub.warehouseId = :warehouseId " +
           "AND FUNCTION('DATE', sub.date) = :targetDate " +
           "GROUP BY sub.itemId" +
       ") sub ON wt.itemId = sub.itemId AND wt.date = sub.latestDate " +
       "WHERE wt.warehouseId = :warehouseId " +
       "AND FUNCTION('DATE', wt.date) = :targetDate")
List<WarehouseTransaction> findLatestDailyStockJoin(
    @Param("warehouseId") Integer warehouseId, 
    @Param("targetDate") String targetDate
);

注意：FUNCTION('DATE', wt.date)是JPA标准的日期转换函数，不同ORM实现（比如Hibernate）也支持DATE(wt.date)或者FUNCTION('DATE_FORMAT', wt.date, '%Y-%m-%d')，可以根据你的实际环境调整。

性能优化建议 #

为了让这个查询在数据量大时依然高效，建议给warehouse_transaction表建立联合索引：

CREATE INDEX idx_warehouse_date_item ON warehouse_transaction(WarehouseId, Date, ItemId);

这个索引会让子查询的分组和MAX操作直接走索引扫描，避免全表遍历，大幅提升查询速度。

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