使用Spring Data实现按外键去重并按价格排序获取记录的问题

前置说明 #

需求拆解为两步：

1. 按产品分类分组，每组取价格最高的产品
2. 所有返回结果按价格排序
   提供两种常用实现方案，可根据业务场景选择：

方案1：JPQL实现（跨数据库兼容） #

在Product对应的Repository接口中添加如下方法：

public interface ProductRepository extends JpaRepository<Product, Long> {
    @Query("SELECT p FROM Product p " +
           "WHERE p.price = (SELECT MAX(p2.price) FROM Product p2 WHERE p2.productCategory.id = p.productCategory.id) " +
           "ORDER BY p.price DESC")
    List<Product> findHighestPricedProductPerCategory();
}

逻辑说明 #

• 子查询先计算每个产品分类对应的最高产品价格
• 外层查询匹配所有价格等于所属分类最高价的产品
• 最终按产品价格倒序返回结果
• 若同分类下存在多个价格相同的最高价产品，会全部返回

方案2：原生SQL实现（单分类唯一返回） #

如果要求每个分类仅返回一条最高价产品，可使用数据库窗口函数实现，以MySQL为例：

public interface ProductRepository extends JpaRepository<Product, Long> {
    @Query(value = "SELECT p.* FROM " +
           "(SELECT *, ROW_NUMBER() OVER(PARTITION BY product_category_id ORDER BY price DESC, id ASC) AS rank_num FROM product) p " +
           "WHERE p.rank_num = 1 " +
           "ORDER BY p.price DESC",
           nativeQuery = true)
    List<Product> findHighestPricedProductPerCategoryUnique();
}

逻辑说明 #

• 使用ROW_NUMBER()窗口函数按分类ID分组，组内按价格倒序、ID升序排序，生成组内排名
• 仅取每组排名为1的记录，保证每个分类仅返回一条产品
• 最终按价格倒序返回全局排序结果

适配调整 #

• 需要升序排序时，将语句末尾的DESC修改为ASC即可
• 需确保实体类中Product与ProductCategory的关联映射、数据库外键字段名与查询语句中的字段保持一致

内容的提问来源于stack exchange，提问作者Yasir Shabbir Choudhary