我正在寻找MySQL等效项或以下查询的等效项：
(select course_id
from section
where semester = 'Fall' and year= 2009)
except
(select course_id
from section
where semester = 'Spring' and year= 2010);
分区表在哪里：
+-----------+--------+----------+------+----------+-------------+--------------+
| course_id | sec_id | semester | year | building | room_number | time_slot_id |
+-----------+--------+----------+------+----------+-------------+--------------+
| BIO-101 | 1 | Summer | 2009 | Painter | 514 | B |
| BIO-301 | 1 | Summer | 2010 | Painter | 514 | A |
| CS-101 | 1 | Fall | 2009 | Packard | 101 | H |
| CS-101 | 1 | Spring | 2010 | Packard | 101 | F |
| CS-190 | 1 | Spring | 2009 | Taylor | 3128 | E |
| CS-190 | 2 | Spring | 2009 | Taylor | 3128 | A |
| CS-315 | 1 | Spring | 2010 | Watson | 120 | D |
| CS-319 | 1 | Spring | 2010 | Watson | 100 | B |
| CS-319 | 2 | Spring | 2010 | Taylor | 3128 | C |
| CS-347 | 1 | Fall | 2009 | Taylor | 3128 | A |
| EE-181 | 1 | Spring | 2009 | Taylor | 3128 | C |
| EE-302 | 1 | Summer | 2010 | Watson | 327 | C |
| FIN-201 | 1 | Spring | 2010 | Packard | 101 | B |
| HIS-351 | 1 | Spring | 2010 | Painter | 514 | C |
| MU-199 | 1 | Spring | 2010 | Packard | 101 | D |
| PHY-101 | 1 | Fall | 2009 | Watson | 100 | A |
+-----------+--------+----------+------+----------+-------------+--------------+
换句话说，我想查找2009年秋季学期而不是2010年春季学期教授的所有课程。
最佳答案
MySQL不支持except，因此只需使用not exists或not in：
select courseid
from section sf
where semester = 'Fall' and year = 2009 and not exists
(select 1
from section ss
where sf.courseid = ss.courseid and ss.semester = 'Spring' and ss.year = 2010
);
（我更喜欢
not exists，因为它更直观地支持NULL值。）这是不正确的，因为
except会删除重复项。您可以使用select distinct，但我怀疑确实需要这样做。