select S.ID , S.name from student as S where not exists (( select course_id from course where dept_name = 'Biology') except ( select T.course_id from takes as T where S.ID = T.ID )); 這個在sql server上運行是沒有問題的,但是如果在myql下運行就是如下報錯:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'excep t ( select T.course_id from takes as T where S.ID = T.ID ))' at line 6 mysql> 因為mysql下不支持 except的命令,所以,我們要換個方式來查詢“找出選修了 Biology 系開設的所有課程的學生”.
其實,not exists(B except A)和 not in 差不多的,所以,我們可以使用下面的sql命令達到查詢要求,先看下student表中的記錄:
mysql> select * from takes; +-------+-----------+--------+----------+------+-------+ | ID | course_id | sec_id | semester | year | grade | +-------+-----------+--------+----------+------+-------+ | 00128 | CS-101 | 1 | Fall | 2009 | A | | 00128 | CS-347 | 1 | Fall | 2009 | A- | | 12345 | CS-101 | 1 | Fall | 2009 | C | | 12345 | CS-190 | 2 | Spring | 2009 | A | | 12345 | CS-315 | 1 | Spring | 2010 | A | | 12345 | CS-347 | 1 | Fall | 2009 | A | | 19991 | HIS-351 | 1 | Spring | 2010 | B | | 23121 | FIN-201 | 1 | Spring | 2010 | C+ | | 44553 | PHY-101 | 1 | Fall | 2009 | B- | | 45678 | CS-101 | 1 | Fall | 2009 | F | | 45678 | CS-101 | 1 | Spring | 2010 | B+ | | 45678 | CS-319 | 1 | Spring | 2010 | B | | 54321 | CS-101 | 1 | Fall | 2009 | A- | | 54321 | CS-190 | 2 | Spring | 2009 | B+ | | 55739 | MU-199 | 1 | Spring | 2010 | A- | | 76543 | CS-101 | 1 | Fall | 2009 | A | | 76543 | CS-319 | 2 | Spring | 2010 | A | | 76653 | EE-181 | 1 | Spring | 2009 | C | | 98765 | CS-101 | 1 | Fall | 2009 | C- | | 98765 | CS-315 | 1 | Spring | 2010 | B | | 98988 | BIO-101 | 1 | Summer | 2009 | A | | 98988 | BIO-301 | 1 | Summer | 2010 | NULL | +-------+-----------+--------+----------+------+-------+ 22 rows in set (0.00 sec) course表中的記錄:
mysql> select * from course; +-----------+----------------------------+------------+---------+ | course_id | title | dept_name | credits | +-----------+----------------------------+------------+---------+ | BIO-101 | Intro. to Biology | Biology | 4 | | BIO-301 | Genetics | Biology | 4 | | BIO-399 | Computational Biology | Biology | 3 | | CS-101 | Intro. to Computer Science | Comp. Sci. | 4 | | CS-190 | Game Design | Comp. Sci. | 4 | | CS-315 | Robotics | Comp. Sci. | 3 | | CS-319 | Image Processing | Comp. Sci. | 3 | | CS-347 | Database System Concepts | Comp. Sci. | 3 | | EE-181 | Intro. to Digital Systems | Elec. Eng. | 3 | | FIN-201 | Investment Banking | Finance | 3 | | HIS-351 | World History | History | 3 | | MU-199 | Music Video Production | Music | 3 | | PHY-101 | Physical Principles | Physics | 4 | +-----------+----------------------------+------------+---------+ 13 rows in set (0.00 sec) 接著看一下'Biology'系總共開了哪些課程:
mysql> select course_id -> from course -> where dept_name = 'Biology'; +-----------+ | course_id | +-----------+ | BIO-101 | | BIO-301 | | BIO-399 | +-----------+ 3 rows in set (0.00 sec) 通過觀察,我們的都能輕易看出,“找出選修了 Biology 系開設的所有課程的學生”的結果是,就只有一個叫Tanaka 上了Biology系開的課程.
所以,我們可以將書上的改成except命令改成:
select distinct S.ID , S.name from student as S ,takes as T where S.ID = T.ID and course_id in ( select course_id from course where dept_name = 'Biology'); --查詢結果： +-------+--------+ | ID | name | +-------+--------+ | 98988 | Tanaka | +-------+--------+ 1 row in set (0.03 sec) 我們將問題改成“找出選修了 Comp. Sci,系開設的所有課程的學生” ,執行:
select distinct S.ID , S.name from student as S ,takes as T where S.ID = T.ID and course_id in ( select course_id from course where dept_name = 'Comp. Sci.'); --查詢結果： +-------+----------+ | ID | name | +-------+----------+ | 00128 | Zhang | | 12345 | Shankar | | 45678 | Levy | | 54321 | Williams | | 76543 | Brown | | 98765 | Bourikas | +-------+----------+ 6 rows in set (0.00 sec)。
