如何在JPA查询中编写MAX和OVER (PARTITION BY)函数？

首先，你遇到的错误根源是HQL不支持在FROM子句中直接使用嵌套子查询（也就是SELECT ... FROM (SELECT ...)这种写法），哪怕窗口函数本身在新版本Hibernate中支持，这种子查询作为数据源的方式也不符合HQL的语法规范。另外你的原查询里还有几处小问题：比如未定义的piu对象、错误地将子查询的别名当作参数引用等。

下面提供两种可行的解决方案：

方案1：使用原生SQL查询（兼容性最好） #

原生SQL直接支持窗口函数和子查询逻辑，适合所有版本的Spring Data JPA：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

// 在你的Repository接口中添加该方法
@Query(value = "SELECT dr.* " +
               "FROM drawing_rate dr " +
               "JOIN drawing d ON dr.drawing_id = d.id " +
               "JOIN user mb ON d.modified_by_id = mb.id " +
               "WHERE (mb.id = :userId OR dr.modified_by_id = :userId) " +
               "AND dr.revision = (" +
                   "SELECT MAX(dr2.revision) " +
                   "FROM drawing_rate dr2 " +
                   "JOIN drawing d2 ON dr2.drawing_id = d2.id " +
                   "WHERE d2.drawing_number = d.drawing_number" +
               ")",
       nativeQuery = true)
List<DrawingRate> findLatestRevisionByModifiedUserId(@Param("userId") Long userId);

说明： #

• 这里通过关联子查询，为每个drawing_number找到对应的最大revision，再匹配原表中符合修改人ID条件的记录
• 注意替换表名和字段名为你数据库中的实际名称（比如drawing_rate对应DrawingRate实体的表，drawing_id是实体间关联的外键等）

方案2：使用HQL的CTE（公共表表达式）+ 窗口函数（Hibernate 5.2+支持） #

如果你的项目使用Hibernate 5.2及以上版本，HQL已经支持CTE和窗口函数，可以用更简洁的面向实体的写法：

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

@Query("WITH rankedDrawingRates AS (" +
       "    SELECT dr, " +
       "           RANK() OVER (PARTITION BY dr.drawing.drawingNumber ORDER BY dr.revision DESC) AS rankNum " +
       "    FROM DrawingRate dr " +
       "    JOIN dr.drawing d " +
       "    JOIN d.modifiedBy mb " +
       "    WHERE mb.id = :userId OR dr.modifiedBy.id = :userId" +
       ") " +
       "SELECT dr FROM rankedDrawingRates WHERE rankNum = 1")
List<DrawingRate> findLatestRevisionByModifiedUserId(@Param("userId") Long userId);

说明： #

• 先用CTE给每个drawingNumber分组的DrawingRate记录按revision降序排名，排名为1的就是该组最大版本的记录
• 这种写法完全基于实体和属性名，不需要关心数据库表结构，更符合JPA的面向对象特性

原查询的问题总结 #

• HQL不允许在FROM子句中嵌套子查询，这是报错的核心原因
• 原查询中piu.Id=:Id里的piu未在查询中定义，推测应该是dr.modifiedBy.id=:Id
• 错误地将子查询中窗口函数的别名latest_revision当作参数:latest_revision引用，这是语法错误

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