JPA中使⽤@Query注解多表联查
sql left join 多表连接原⽣SQL：
select `user`.id, `user`.`name`,dept.name deptName,sum(sd.score) SumScore
from `user`
LEFT JOIN
score_detail sd
ON
`user`.id = sd.user_id
LEFT JOIN
department dept
ON
`user`.department_id = dept.id
GROUP BY `user`.`id` ORDER BY sum(sd.score);
经过查阅多⽅资料得知，
异常: org.hibernate.hql.ast.QuerySyntaxException: Path expected for join
HQL是⾯向对象的，所以join的条件是反映在对象的关系中的，所以，你在⽤join的时候，⽆论是left join还是right join，都是FROM DomainA a LEFT JOIN a.b b WHERE
或者
FROM DomainA a RIGHT JOIN a.b b WHERE
⽽不能直接是两个对象直接JOIN。
最终结果：
@Query("SELECT sm.pojo.vo.SumScoreRankVO(u.id, u.name, d.name, SUM(s.score)) "
+ "FROM ScoreDetail s "
+ "RIGHT JOIN s.user u "
+ "RIGHT JOIN u.department d "
+ "ateDate BETWEEN ?1 AND ?2 "
+ "GROUP BY u.id "
+ "ORDER BY SUM(s.score) DESC")
List<SumScoreRankVO> findUserScoreRank(Date startDate, Date endDate);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。