Spring Data JPA原生查询筛选同时持有全部指定CourseID的学生记录

问题根源 #

• 你传入逗号拼接的字符串"A01,A02"作为参数时，Oracle不会自动将其拆分为多个独立的课程ID值，SQL实际执行时会匹配CourseID等于整个字符串的记录，自然无结果返回。
• 直接使用IN关键字不加额外限制时，SQL逻辑为匹配任意一个传入的课程ID，返回的是选修了任意一门指定课程的学生，不符合同时选修所有课程的需求。

通用实现方案 #

你可以通过「分组计数匹配」的逻辑实现需求，该方案支持任意数量的课程ID传入，性能稳定且SQL逻辑简洁：

1. 修正Repository层代码

将参数改为字符串集合类型，使用分组后统计去重课程数量的逻辑匹配同时选满所有课程的学生：

@Query(value = "SELECT DISTINCT s.* FROM STUDENT s " +
        "WHERE s.StudentID IN (" +
        "    SELECT sc.StudentID FROM STUDENT_COURSE sc " +
        "    WHERE sc.CourseID IN :courseIds " +
        "    GROUP BY sc.StudentID " +
        "    HAVING COUNT(DISTINCT sc.CourseID) = :courseCount" +
        ")", nativeQuery = true)
List<Student> advancedSearch(@Param("courseIds") List<String> courseIds, @Param("courseCount") Integer courseCount);

2. 调用方法

传入要匹配的课程ID集合，以及集合的长度即可：

// 示例：查询同时选修A01、A02的学生
List<String> targetCourses = List.of("A01", "A02");
List<Student> students = studentRepository.advancedSearch(targetCourses, targetCourses.size());

逻辑说明 #

子查询会先过滤出所有选修了任意一门指定课程的中间表记录，按学生ID分组后，统计每个学生匹配到的不同课程数量，只有数量等于传入的课程总数的学生，才是同时选修了所有指定课程的学生。

补充说明：如果你的STUDENT_COURSE表已设置StudentID和CourseID为联合主键，保证不会有重复的关联记录，可以将COUNT(DISTINCT sc.CourseID)简化为COUNT(sc.CourseID)，性能会更好。

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