我想在一种SQL语法中使用DISTINCT,NOT EXISTS,adn EXCEPT,但是它不起作用.这是我的SQL语法:
SELECT DISTINCT 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)
);
这是错误:
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
'EXCEPT (SELECT T.course_id FROM takes AS T WHERE S.ID = T.ID))' at line 1
如何使它起作用,我解决不了,伙计们帮助我..
解决方法:
AFAIK,MySQL中没有EXCEPT.使用NOT IN就像
SELECT DISTINCT S.ID, S.name
FROM student AS S
WHERE NOT EXISTS(
SELECT course_id FROM course
WHERE dept_name = 'Biology'
AND course_id NOT IN
(SELECT T.course_id FROM takes AS T
WHERE S.ID = T.ID)
);
标签:sql,mysql,database
来源: https://codeday.me/bug/20191027/1948221.html