mysql - 如何获得 subject with and or 条件？

Question

select distinct a1.id
from m a1, m a2
on a1.Id = a2.Id
where a1.sub = 'physics' and a2.sub <> 'chem';

我想要一个学过物理但没有学过化学的学生？

给我错误的结果？

有什么输入吗？

Answer 1

通过使用不存在:

select * 
from m a1
where a1.sub = 'physics'  
and not exists (select 1 from m where id = a1.id and sub = 'chem')