Spring Data+Hibernate+SQL Server中Distinct分页查询异常解决

针对SQL Server要求SELECT DISTINCT必须包含ORDER BY字段的限制，提供以下几种可行方案：

方案一：先分页查询ID，再批量获取实体 #

核心思路是拆分查询：先通过带DISTINCT的分页查询获取符合条件的实体ID列表，再根据ID批量查询完整实体。既避免了关联查询导致的数据重复，又规避了ORDER BY字段不在SELECT列表的问题。

代码实现 #

1. 在Repository中新增ID分页查询方法：

@Query("select distinct nat.id from TABE1 nat " +
        "left join TABLE2 natr on natr.ID= nat.ID" +
        "left join TABLE3 rec on rec.ID= natr.ID_" +
        "left join TABLE4 ew on ew.ID= rec.ID_ " +
        "where (ew.status = 'VA' or nat.isM = true) " +
        "and nat.expDate = :endTime " +
        "and nat.dDate = :endTime"
)
Page<Long> findAllActiveHIds(@Param("endTime") LocalDateTime endOfTime, Pageable pageable);

2. 在Service层封装完整查询逻辑：

public Page<Entity1> findAllActiveH(LocalDateTime endOfTime, Pageable pageable) {
    // 先分页查询ID列表
    Page<Long> idPage = entityRepository.findAllActiveHIds(endOfTime, pageable);
    // 根据ID批量获取实体
    List<Entity1> entities = entityRepository.findAllById(idPage.getContent());
    // 保持分页元数据（页码、每页数量、总条数）一致
    return new PageImpl<>(entities, pageable, idPage.getTotalElements());
}

方案二：确保ORDER BY字段包含在SELECT DISTINCT列表中 #

如果排序字段是关联表的属性（如ew.status），需将该字段添加到JPQL的SELECT列表中，同时通过投影或构造器转换为目标实体。

代码示例（使用Tuple投影） #

@Query("select distinct nat, ew.status from TABE1 nat " +
        "left join TABLE2 natr on natr.ID= nat.ID" +
        "left join TABLE3 rec on rec.ID= natr.ID_" +
        "left join TABLE4 ew on ew.ID= rec.ID_ " +
        "where (ew.status = 'VA' or nat.isM = true) " +
        "and nat.expDate = :endTime " +
        "and nat.dDate = :endTime " +
        "order by ew.status"
)
Page<Tuple> findAllActiveHWithSortField(@Param("endTime") LocalDateTime endOfTime, Pageable pageable);

在Service层将Tuple转换为Entity1：

public Page<Entity1> convertToEntityPage(Page<Tuple> tuplePage, Pageable pageable) {
    List<Entity1> entities = tuplePage.getContent().stream()
            .map(tuple -> (Entity1) tuple.get(0))
            .distinct() // 可选，确保最终实体无重复
            .collect(Collectors.toList());
    return new PageImpl<>(entities, pageable, tuplePage.getTotalElements());
}

方案三：使用原生SQL查询 #

直接编写符合SQL Server语法的原生SQL，明确将ORDER BY字段包含在SELECT DISTINCT中，同时指定count查询避免分页计数错误。

@Query(value = "select distinct nat.*, ew.status from TABLE1 nat " +
        "left join TABLE2 natr on natr.ID= nat.ID " +
        "left join TABLE3 rec on rec.ID= natr.ID_ " +
        "left join TABLE4 ew on ew.ID= rec.ID_ " +
        "where (ew.status = 'VA' or nat.isM = 1) " +
        "and nat.expDate = :endTime " +
        "and nat.dDate = :endTime " +
        "order by ew.status",
        countQuery = "select count(distinct nat.id) from TABLE1 nat " +
        "left join TABLE2 natr on natr.ID= nat.ID " +
        "left join TABLE3 rec on rec.ID= natr.ID_ " +
        "left join TABLE4 ew on ew.ID= rec.ID_ " +
        "where (ew.status = 'VA' or nat.isM = 1) " +
        "and nat.expDate = :endTime " +
        "and nat.dDate = :endTime",
        nativeQuery = true)
Page<Entity1> findAllActiveH(@Param("endTime") LocalDateTime endOfTime, Pageable pageable);

注意：如果排序字段由Pageable动态传入，需确保字段存在于SELECT列表中，可通过SpEL表达式动态拼接排序部分。

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