JPQL如何用CASE WHEN实现无评分电影平均评分按0计算的查询

解决方案 #

完全可以在单条JPQL查询中实现该需求，无需额外业务层逻辑处理。

推荐写法（使用COALESCE函数，更简洁） #

COALESCE是JPQL标准支持的空值处理函数，会返回参数列表中第一个非空的值，刚好可以把没有评分时返回的null转换为0：

@Query(value = 
    "select m from Movie m " +
    "where COALESCE((select avg(r.rating) from Review r where m.id = r.movie.id), 0) < :rating")
List<Movie> findMoviesWithAverageRatingLowerThan(@Param("rating") Double rating);

CASE WHEN 写法（符合你提到的条件分支实现思路） #

如果你需要用CASE WHEN语法实现，写法如下，逻辑和上面完全一致：

@Query(value = 
    "select m from Movie m " +
    "where CASE " +
    "WHEN (select avg(r.rating) from Review r where m.id = r.movie.id) IS NULL THEN 0 " +
    "ELSE (select avg(r.rating) from Review r where m.id = r.movie.id) " +
    "END < :rating")
List<Movie> findMoviesWithAverageRatingLowerThan(@Param("rating") Double rating);

逻辑说明 #

当Movie没有关联的Review数据时，avg(r.rating)的计算结果为null，两种写法都会把null替换为0参与比较，完全符合业务要求。两种写法执行效率没有差异，更推荐简洁的COALESCE方案。

内容的提问来源于stack exchange，提问作者hc0re