Spring Data JPA中DISTINCT搭配ORDER BY使用报错问题咨询

• 初始报错ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list是PostgreSQL等数据库的SQL规范强制要求：使用DISTINCT关键字做结果去重时，所有ORDER BY子句引用的字段必须包含在SELECT返回列中，否则SQL直接执行失败。
• 修改为SELECT DISTINCT b, c.id后仍出现重复数据，是因为此时DISTINCT的去重维度是(Book实体, c.id)的二元组合：一本书关联多个分类时，不同分类id会生成多条不同的组合记录，自然返回重复的Book对象，且该查询返回结果是Object[]类型，本身就无法直接映射为Page<Book>。
• 额外注意：原代码中JpaRepository<Book, Integer>的主键泛型定义错误，Book实体的主键为String类型，需要修正为JpaRepository<Book, String>，否则会出现主键类型转换异常。

方案1：自定义count查询+Hibernate实体去重（最推荐） #

显式指定分页查询需要的count语句，同时开启Hibernate的内存级父实体去重配置，不需要修改JPQL的查询结构：

1. 修正Repository层查询代码：

@Repository
public interface BookDao extends JpaRepository<Book, String> {
    @Query(
        value = "SELECT DISTINCT b FROM Book b JOIN b.category c ORDER BY c.id DESC",
        countQuery = "SELECT COUNT(DISTINCT b) FROM Book b JOIN b.category c"
    )
    Page<Book> getByBookIdDESC(Pageable pageable);
}

2. 在Spring Boot配置文件中添加配置，关闭DISTINCT直接透传给数据库的逻辑，由Hibernate在内存中完成Book实体去重：

spring.jpa.properties.hibernate.query.pass_distinct_through=false

该方案不会返回冗余字段，查询性能最优。

方案2：使用子查询绕开数据库DISTINCT校验 #

如果不想修改全局Hibernate配置，可以将排序逻辑放到子查询中，规避ORDER BY字段和DISTINCT的校验冲突：

@Repository
public interface BookDao extends JpaRepository<Book, String> {
    @Query(
        value = "SELECT DISTINCT b FROM Book b WHERE b IN (SELECT b2 FROM Book b2 JOIN b2.category c ORDER BY c.id DESC)",
        countQuery = "SELECT COUNT(DISTINCT b) FROM Book b JOIN b.category c"
    )
    Page<Book> getByBookIdDESC(Pageable pageable);
}

方案3：用EXISTS+聚合函数排序，从根源避免笛卡尔积 #

如果业务仅需要按照关联分类的id排序，不需要在查询中关联全部分类数据，可以用EXISTS代替JOIN，同时用聚合函数取分类id作为排序依据，完全避免多表关联产生的重复行，不需要使用DISTINCT：

@Repository
public interface BookDao extends JpaRepository<Book, String> {
    @Query(
        value = "SELECT b FROM Book b WHERE EXISTS (SELECT 1 FROM b.category c) ORDER BY (SELECT MAX(c.id) FROM b.category c) DESC",
        countQuery = "SELECT COUNT(b) FROM Book b WHERE EXISTS (SELECT 1 FROM b.category c)"
    )
    Page<Book> getByBookIdDESC(Pageable pageable);
}

这里使用MAX(c.id)是因为单本书对应多个分类，取关联分类的最大id作为排序维度，不会产生多表关联的笛卡尔积，性能比JOIN方案更高。

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