java - hibernate :尽管列存在，MySQL错误在having子句中出现未知列

我在存储库中有一个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