在查询中使用except时出错?mysql
该查询有效：
mysql> SELECT s.sno FROM students s;
+------+
| sno |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (0.00 sec)
此查询也适用：
mysql> SELECT t.sno FROM take t WHERE t.cno = 'CS112';
+------+
| sno |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
但是这个查询：
SELECT s.sno FROM students s
EXCEPT
SELECT t.sno FROM take t WHERE t.cno = 'CS112';
失败并显示以下错误：
mysql> SELECT s.sno FROM students s
-> EXCEPT
-> SELECT t.sno FROM take t WHERE t.cno = 'CS112';
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 n
ear 'EXCEPT
SELECT t.sno FROM take t WHERE t.cno = 'CS112'' at line 2 我在这里做错了什么？
版权声明：本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
-
我不相信MySQL支持EXCEPT语法。尝试使用NOT IN或LEFT JOIN：
SELECT s.sno FROM students s
WHERE s.sno NOT IN ( SELECT t.sno FROM take t WHERE t.cno = 'CS112' ); 要么SELECT s.sno FROM students s
LEFT JOIN take t ON s.sno = t.sno WHERE IFNULL(t.cno, '') != 'CS112' 更新我照样模拟了您的数据，它正确返回了5到10：
create temporary table temp_students (sno int)
insert into temp_students values (1) insert into temp_students values (2) insert into temp_students values (3) insert into temp_students values (4) insert into temp_students values (5) insert into temp_students values (6) insert into temp_students values (7) insert into temp_students values (8) insert into temp_students values (9) insert into temp_students values (10)
create temporary table temp_take (sno int, cno varchar(50))
insert into temp_take values (1, 'CS112') insert into temp_take values (2, 'CS112') insert into temp_take values (3, 'CS112') insert into temp_take values (4, 'CS112')
SELECT s.sno FROM temp_students s
LEFT JOIN temp_take t ON s.sno = t.sno WHERE IFNULL(t.cno, '') != 'CS112'来源：stack overflow2020-05-17 11:04:34赞同 展开评论