mysql - 我想显示学生ID，谁拥有以上5个单元？

Question

你能帮我解决这个sql查询吗，

select STUD_ID,unit_c 
from FCHE_grad 
where (unit_c in("C0001","C0002","ENG300","K0001","K0002")) 
order by STUD_ID

上面的查询返回student_id，他有4个unit。我想显示学生ID，谁拥有以上5个单元？

Answer 1

使用having子句检查学生是否已完成所有 5 个单元

select * from FCHE_grad
where stud_id in (select STUD_ID
from FCHE_grad 
where (unit_c in('C0001','C0002','ENG300','K0001','K0002')) 
group by STUD_ID
having count(stud_id)=5);

Fiddle