如何在JPA中查询指定时间范围内每日对应的最新时间戳记录

你之前的写法问题在于子查询没有按天分组，仅返回了整个时间范围内全局最大的endOfValidity对应的记录，要实现按天取最新，需要对endOfValidity做日期截断后分组，取每天的最大endOfValidity再关联主查询，具体修改如下：

核心调整点 #

• 子查询新增按日期维度分组逻辑，通过数据库日期截断函数提取endOfValidity的日期部分
• 子查询新增和主查询的业务维度（区域、门店、品类等）关联条件，避免跨业务维度匹配
• 原有业务过滤条件需要同时应用在主查询和子查询中，保证子查询取的最大值是对应筛选条件下的每天最大值

完整修改后代码 #

以下示例默认适配MySQL数据库，如果你使用Oracle等其他数据库，仅需调整日期截断函数名即可：

public Optional<List<ShopsTable>> findByPeriod(String regionNumber,
                                               String shopNumber,
                                               String category,
                                               String workerId,
                                               String currency,
                                               LocalDateTime startOfValidity,
                                               LocalDateTime endOfValidity) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<ShopsTable> cq = cb.createQuery(ShopsTable.class);
    Root<ShopsTable> tableRoot = cq.from(ShopsTable.class);

    // 主查询基础过滤条件
    List<Predicate> mainPredicates = new ArrayList<>();
    mainPredicates.add(cb.equal(tableRoot.get("regionNumber"), regionNumber));
    mainPredicates.add(cb.equal(tableRoot.get("shopNumber"), shopNumber));
    mainPredicates.add(cb.equal(tableRoot.get("category"), category));
    mainPredicates.add(cb.equal(tableRoot.get("workerId"), workerId));
    mainPredicates.add(cb.equal(tableRoot.get("currency"), currency));
    mainPredicates.add(cb.greaterThanOrEqualTo(tableRoot.get("startOfValidity"), startOfValidity));
    mainPredicates.add(cb.lessThanOrEqualTo(tableRoot.get("endOfValidity"), endOfValidity));

    // 构造子查询：获取筛选条件下每天的最大endOfValidity
    Subquery<Timestamp> maxSubQuery = cq.subquery(Timestamp.class);
    Root<ShopsTable> subRoot = maxSubQuery.from(ShopsTable.class);

    // 子查询过滤条件：和主查询的业务规则、时间范围完全对齐
    List<Predicate> subPredicates = new ArrayList<>();
    subPredicates.add(cb.equal(subRoot.get("regionNumber"), regionNumber));
    subPredicates.add(cb.equal(subRoot.get("shopNumber"), shopNumber));
    subPredicates.add(cb.equal(subRoot.get("category"), category));
    subPredicates.add(cb.equal(subRoot.get("workerId"), workerId));
    subPredicates.add(cb.equal(subRoot.get("currency"), currency));
    subPredicates.add(cb.greaterThanOrEqualTo(subRoot.get("startOfValidity"), startOfValidity));
    subPredicates.add(cb.lessThanOrEqualTo(subRoot.get("endOfValidity"), endOfValidity));
    // 关联主查询和子查询的日期维度（MySQL用DATE函数截断日期，Oracle可改为TRUNC）
    subPredicates.add(cb.equal(
            cb.function("DATE", LocalDate.class, tableRoot.get("endOfValidity")),
            cb.function("DATE", LocalDate.class, subRoot.get("endOfValidity"))
    ));

    // 子查询按日期分组，返回每组的最大endOfValidity
    maxSubQuery.select(cb.greatest(subRoot.<Timestamp>get("endOfValidity")))
            .where(subPredicates.toArray(new Predicate[0]))
            .groupBy(cb.function("DATE", LocalDate.class, subRoot.get("endOfValidity")));

    // 主查询仅保留符合当日最大endOfValidity的记录
    mainPredicates.add(cb.equal(tableRoot.get("endOfValidity"), maxSubQuery));
    cq.where(mainPredicates.toArray(new Predicate[0]));

    return Optional.of(em.createQuery(cq).getResultList());
}

注意事项 #

• 若你使用Oracle数据库，将代码中所有cb.function("DATE", ...)的第一个参数改为TRUNC即可
• 如果同一天存在多条记录endOfValidity完全相同的情况，会返回当天所有符合最大值的记录，可根据业务需要额外新增排序或去重逻辑调整

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