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