使用Hibernate与Oracle筛选仅选修指定科目的学生

没问题！不管是用HQL还是纯SQL，都能精准筛选出只选修了数学和科学这两门课的学生，我给你详细说说两种实现方式：

结合你提供的双向关联POJO，有两种比较直观的写法：

写法一：关联查询+分组+排除其他科目 #

SELECT s 
FROM Student s 
JOIN s.lstSubject sub 
WHERE sub.sub_name IN ('数学', '科学') 
GROUP BY s.std_id, s.std_name 
HAVING COUNT(DISTINCT sub.sub_id) = 2
AND NOT EXISTS (
    SELECT 1 
    FROM Student s2 
    JOIN s2.lstSubject sub2 
    WHERE s2.std_id = s.std_id 
    AND sub2.sub_name NOT IN ('数学', '科学')
)

• 逻辑说明：先关联学生和他们的科目，筛选出选了数学或科学的学生；分组后通过COUNT(DISTINCT sub.sub_id) = 2确保这两门课都选了；最后用NOT EXISTS排除掉还选修了其他科目的学生，完美符合“不多选、不少选”的要求。

写法二：直接利用集合属性判断 #

SELECT s 
FROM Student s 
WHERE SIZE(s.lstSubject) = 2 
AND EXISTS (
    SELECT 1 FROM s.lstSubject sub WHERE sub.sub_name = '数学'
) 
AND EXISTS (
    SELECT 1 FROM s.lstSubject sub WHERE sub.sub_name = '科学'
)

• 逻辑说明：这种写法更简洁，先要求学生的科目集合大小刚好是2（保证没有多选），再分别检查集合里存在数学和科学（保证没有少选）。注意前提是你的Student_Subject关联表没有重复数据，也就是一个学生不会重复选同一门课。

纯SQL的思路和HQL类似，直接基于三张表的关联关系来写：

写法一：关联查询+分组+排除其他科目 #

SELECT s.std_id, s.std_name
FROM Student s
JOIN Student_Subject ss ON s.std_id = ss.std_id
JOIN Subject sub ON ss.sub_id = sub.sub_id
WHERE sub.sub_name IN ('数学', '科学')
GROUP BY s.std_id, s.std_name
HAVING COUNT(DISTINCT sub.sub_id) = 2
AND NOT EXISTS (
    SELECT 1
    FROM Student_Subject ss2
    JOIN Subject sub2 ON ss2.sub_id = sub2.sub_id
    WHERE ss2.std_id = s.std_id
    AND sub2.sub_name NOT IN ('数学', '科学')
)

写法二：子查询统计科目情况 #

SELECT s.std_id, s.std_name
FROM Student s
JOIN (
    SELECT ss.std_id
    FROM Student_Subject ss
    JOIN Subject sub ON ss.sub_id = sub.sub_id
    GROUP BY ss.std_id
    HAVING SUM(CASE WHEN sub.sub_name = '数学' THEN 1 ELSE 0 END) = 1
    AND SUM(CASE WHEN sub.sub_name = '科学' THEN 1 ELSE 0 END) = 1
    AND COUNT(DISTINCT sub.sub_id) = 2
) filtered_students ON s.std_id = filtered_students.std_id

• 逻辑说明：子查询里统计每个学生选数学、科学的次数都是1（确保两门都选了），同时总科目数是2（确保没有多选其他），最后关联学生表拿到学生信息。

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