JPA查询演员共同出演影片异常：IN子句返回单人参演影片

问题根源 #

你的查询用IN :lastNames的逻辑是：只要影片有任意一位 lastName 在列表中的演员参演，就会被返回。这就是为什么结果是所有演员单独参演影片的合集，而非所有演员共同出演的影片。

修复方案：分组统计匹配演员数量 #

通过分组统计每部影片对应的匹配演员数量，筛选出数量与传入演员列表长度一致的影片，就能确保这些影片包含所有传入的演员。

修改后的查询代码如下（优化了冗余的LEFT JOIN，并添加分组与筛选逻辑）：

@Query(value = "SELECT new it.package.Project.payload.response.FilmResponse(" +
        "fs.filmStaffId.film.filmId, " +
        "fs.filmStaffId.film.title, " +
        "fs.filmStaffId.film.description, " +
        "fs.filmStaffId.film.releaseYear, " +
        "fs.filmStaffId.film.languageId.languageName) " +
        "FROM FilmStaff fs " +
        "WHERE fs.filmStaffId.role.roleName = 'ACTOR' " +
        "AND fs.filmStaffId.staff.lastName IN :lastNames " +
        "GROUP BY fs.filmStaffId.film.filmId, fs.filmStaffId.film.title, fs.filmStaffId.film.description, fs.filmStaffId.film.releaseYear, fs.filmStaffId.film.languageId.languageName " +
        "HAVING COUNT(DISTINCT fs.filmStaffId.staff.lastName) = :lastNameCount")
List<FilmResponse> getByActors(@Param("lastNames") Collection<String> lastNames, @Param("lastNameCount") int lastNameCount);

关键说明：

1. GROUP BY：按影片的唯一字段分组，确保每部影片只统计一次。
2. COUNT(DISTINCT ...)：避免同一演员因同一影片有多条记录（比如多角色）导致统计重复，确保每个演员只被计数一次。
3. HAVING 条件：只有当影片匹配的演员数量等于传入列表的长度时，才会被返回，保证所有演员都参演了该影片。

调用方式：

调用时传入演员 lastName 列表和列表的长度，比如：

List<String> actorLastNames = Arrays.asList("Smith", "Johnson");
List<FilmResponse> commonFilms = filmStaffRepository.getByActors(actorLastNames, actorLastNames.size());

可选方案：动态EXISTS子查询（适合固定数量演员） #

如果你的演员数量固定，可以用多个EXISTS子查询逐一验证每个演员是否参演了影片，但这种方式不支持动态数量的演员列表：

@Query(value = "SELECT new it.package.Project.payload.response.FilmResponse(" +
        "f.filmId, f.title, f.description, f.releaseYear, f.languageId.languageName) " +
        "FROM Film f " +
        "WHERE EXISTS (" +
            "SELECT 1 FROM FilmStaff fs " +
            "WHERE fs.filmStaffId.film = f " +
            "AND fs.filmStaffId.role.roleName = 'ACTOR' " +
            "AND fs.filmStaffId.staff.lastName = :lastName1" +
        ") " +
        "AND EXISTS (" +
            "SELECT 1 FROM FilmStaff fs " +
            "WHERE fs.filmStaffId.film = f " +
            "AND fs.filmStaffId.role.roleName = 'ACTOR' " +
            "AND fs.filmStaffId.staff.lastName = :lastName2" +
        ")")
List<FilmResponse> getByTwoActors(@Param("lastName1") String lastName1, @Param("lastName2") String lastName2);

内容的提问来源于stack exchange，提问作者Antonio-93