Spring Data JPA+Amazon Aurora DB：单查询获取最小Price对应的updateDate

结合Spring Data JPA和Amazon Aurora的场景，我给你几种实用的方案，帮你一次性拿到分组后最小Price对应的updateDate：

1. JPQL子查询方案（跨数据库兼容） #

假设你的实体类是Product，包含category（分组字段）、price、updateDate等字段，可以通过子查询先定位每个分组的最小Price，再关联主表拿到对应的updateDate：

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("SELECT p.category, p.price, p.updateDate FROM Product p " +
           "WHERE (p.category, p.price) IN (" +
               "SELECT p2.category, MIN(p2.price) FROM Product p2 GROUP BY p2.category)")
    List<Object[]> findMinPriceWithUpdateDateByCategory();
}

如果想直接返回封装好的DTO（避免处理Object数组），可以先定义一个DTO类：

public class MinPriceInfo {
    private String category;
    private BigDecimal price;
    private LocalDateTime updateDate;

    // 必须提供对应参数的构造函数
    public MinPriceInfo(String category, BigDecimal price, LocalDateTime updateDate) {
        this.category = category;
        this.price = price;
        this.updateDate = updateDate;
    }

    // Getters
}

然后修改查询语句用构造函数投影：

@Query("SELECT new com.yourpackage.MinPriceInfo(p.category, p.price, p.updateDate) FROM Product p " +
       "WHERE (p.category, p.price) IN (" +
           "SELECT p2.category, MIN(p2.price) FROM Product p2 GROUP BY p2.category)")
List<MinPriceInfo> findMinPriceWithUpdateDateByCategory();

2. 原生SQL方案（适配Amazon Aurora） #

因为Amazon Aurora兼容MySQL或PostgreSQL，用原生SQL的JOIN方式性能可能更优，尤其数据量较大时：

针对MySQL兼容的Aurora： #

@Query(value = "SELECT p.category, p.price, p.update_date FROM product p " +
               "JOIN (" +
                   "SELECT category, MIN(price) AS min_price FROM product GROUP BY category) p_min " +
               "ON p.category = p_min.category AND p.price = p_min.min_price", 
       nativeQuery = true)
List<Object[]> findMinPriceWithUpdateDateByCategoryNative();

注意点： #

如果同一个分组内有多个记录的Price都是最小值，以上两种方案会返回所有对应的updateDate。如果你只需要其中一个（比如最新的updateDate），可以用窗口函数实现。

3. 窗口函数方案（适合Aurora MySQL 8+/PostgreSQL） #

利用ROW_NUMBER()窗口函数，按分组划分后，先按Price升序、再按updateDate降序排序，取每个分组的第一行：

@Query("SELECT p.category, p.price, p.updateDate FROM (" +
           "SELECT p2, ROW_NUMBER() OVER (" +
               "PARTITION BY p2.category ORDER BY p2.price ASC, p2.updateDate DESC) AS rn " +
           "FROM Product p2) p " +
       "WHERE p.rn = 1")
List<MinPriceInfo> findMinPriceWithLatestUpdateDateByCategory();

这个方案能确保每个分组只返回一条记录：最小Price里最新的updateDate。

内容的提问来源于stack exchange，提问作者Nunyet Calçada