我在存储库中有一个JPQL查询,相当于下面的MySQL查询:

SELECT DISTINCT ji.* FROM tracker_job_item AS ji
JOIN tracker_work AS w ON ji.id=w.tracker_job_item_id
JOIN tracker_work_quantity AS wq on w.id=wq.tracker_work_id
WHERE w.work_type = 'CUTTING' AND ji.is_finished=0
GROUP BY wq.tracker_work_id
HAVING ji.quantity != SUM(wq.received_quantity)

MySQL版本工作得很好,但是JPQL的等价物给出了一个异常:Unknown column 'jobitem0_.quantity' in 'having clause'
JPQL查询如下:
@Query("select distinct ji from JobItem ji" +
        "   join Work w on ji.id=w.jobItem.id" +
        "   join WorkQuantity wq on w.id=wq.work.id" +
        "   where w.workType='CUTTING' and ji.isFinished=false and ji.jobItemName like %:search%" +
        "   group by ji.id" +
        "   having ji.quantity != sum(wq.receivedQuantity)")
    Page<JobItem> findAllActiveCuttingJobs(Pageable pageable, @Param("search") String search);

请帮助我了解为什么我会得到错误,即使字段quantity存在于JobItem

最佳答案

不能在having子句中引用不在group by子句中的列,在JPA和(通常至少)在SQL中是这样。看起来MySQL让你逃脱了,但JPA却没有。
请看这里:
http://learningviacode.blogspot.co.uk/2012/12/group-by-and-having-clauses-in-hql.html

09-05 17:37