java - 我想将 sql 查询转换为基于 Spring Data JPA 的查询

Question

下面是我的 SQL 查询:

SELECT
  maker_code
FROM
  maker
WHERE
  part_no = p_part_no
AND effect_date_in <= TODAY
AND effect_date_out > TODAY 
AND effect_date_in = (SELECT MAX(effect_date_in) FROM maker WHERE part_no =p_part_no)

我想将 Spring Data JPA 与内置方法一起使用，例如 findBy---()，它将返回与上述 sql 查询相同的结果

我尝试了 JPQL 查询:

 @Query(value = "
    SELECT p FROM Pg6p0012_01PurpartQueryModel p where p.partNo = :p_part_no AND 
    p.effectDateIn <= TODAY AND p.effectDateOut > TODAY AND p.effectDateIn = 
    (
    select max(p.effectDateIn)from Pg6p0012_01PurpartQueryModel where partNo =:p_part_no)"
    )

public List<Pg6p0012_01PurpartQueryModel>findByPartNo(@Param("p_part_no") String p_part_no);

但是我得到一个错误:在此上下文中错误地使用聚合。

Answer 1

您不能从聚合函数的嵌套查询中访问别名 p，例如 MAX。嵌套查询必须有自己的表别名。像这样的东西:

 @Query(value = "
    SELECT p FROM Pg6p0012_01PurpartQueryModel p where p.partNo = :p_part_no AND 
    p.effectDateIn <= TODAY AND p.effectDateOut > TODAY AND p.effectDateIn = 
    (SELECT MAX(p2.effectDateIn) FROM Pg6p0012_01PurpartQueryModel p2 WHERE partNo =:p_part_no)"
 )