无法将SQL查询转换为JPQL(Eclipselink)

2020年2月4日 38点热度 0条评论

大家好,我有以下查询,为了我的生命,我似乎无法将其转换为JPQL。有效的SQL是:

select * from TB_PRINT_DETAIL y inner join 
(select JOB_ID,max(COPY_NUM) MAX_COPY_NUM from TB_PRINT_DETAIL  group by JOB_ID  ) x
on y.JOB_ID = x.JOB_ID and y.COPY_NUM = x.MAX_COPY_NUM

我对此的微弱尝试如下:

select o from PrintDetailEntity o inner join (select o2.jobId, max(o2.copyNumber) as
maxCopyNum from PrintDetailEntity o2 group by o2.jobId ) as x on o.jobId = o2.jobId and
o.copyNum = o2.maxCopyNum where o.printSuppressionReasonEntity is null

在此先感谢您能发出的任何光芒!

解决方案如下:

如果我理解您的查询权限(在具有相同copyNumber的实体中选择具有最大jobId的实体),则应该可以执行以下操作:

SELECT o 
FROM PrintDetailEntity o 
WHERE o.copyNumber = 
    (SELECT MAX(e.copyNumber) FROM PrintDetailEntity e WHERE o.jobId = e.jobId)