如何将含LATERAL派生表的SQL查询转换为JPQL？

JPQL本身不支持LATERAL关键字，无法直接照搬原MySQL写法，但可以通过两种等价方案实现相同逻辑，避免使用原生查询：

方案1：关联子查询实现核心逻辑 #

假设你的实体类定义如下：

• Salesperson：对应salesperson表，包含id、name字段
• AllSales：对应all_sales表，包含salesperson（关联Salesperson的实体对象）、amount、customerName字段

适配Spring Data @Query的JPQL代码：

@Query("SELECT sp.name, maxSale.amount, asl.customerName " +
       "FROM Salesperson sp " +
       "JOIN (SELECT s.salesperson.id, MAX(s.amount) as amount " +
             "FROM AllSales s " +
             "GROUP BY s.salesperson.id) maxSale ON maxSale.id = sp.id " +
       "JOIN AllSales asl ON asl.salesperson.id = sp.id AND asl.amount = maxSale.amount")
List<Object[]> findSalespersonMaxSales();

逻辑说明：

• 子查询先按销售分组，计算每个销售的最大销售额
• 主查询关联该子查询，再匹配销售订单表，拿到对应最大金额的客户名称

方案2：窗口函数（JPA 2.2+支持） #

如果你的JPA版本支持窗口函数（如Hibernate 5.2+、EclipseLink 2.7+），可以用更简洁的方式实现：

@Query("SELECT sp.name, ranked.amount, ranked.customerName " +
       "FROM (SELECT s.salesperson, s.amount, s.customerName, " +
             "ROW_NUMBER() OVER (PARTITION BY s.salesperson.id ORDER BY s.amount DESC) rn " +
             "FROM AllSales s) ranked " +
       "JOIN ranked.salesperson sp " +
       "WHERE ranked.rn = 1")
List<Object[]> findSalespersonMaxSales();

逻辑说明：

• 子查询用窗口函数给每个销售的订单按金额降序编号，最大金额的订单编号为1
• 主查询筛选出编号为1的记录，直接获取每个销售的最高额订单信息

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