MySQL 在查询中使用 except 时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16092353/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error when using except in a query
提问by Cratylus
This query works:
此查询有效：
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)
This query also works:
此查询也有效：
mysql> SELECT t.sno FROM take t WHERE t.cno = 'CS112';
+------+
| sno |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0.00 sec)
BUT this query:
但是这个查询：
SELECT s.sno FROM students s
EXCEPT
SELECT t.sno FROM take t WHERE t.cno = 'CS112';
fails with the error:
失败并出现错误：
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
What am I doing wrong here?
我在这里做错了什么？
回答by McCee
I don't believe MySQL supports EXCEPT syntax. Try using NOT INor a LEFT JOIN:
我不相信 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'
);
OR
或者
SELECT s.sno
FROM students s
LEFT JOIN take t ON s.sno = t.sno
WHERE IFNULL(t.cno, '') != 'CS112'
UPDATE
更新
I mocked up your data as such and it correctly returns 5 through 10:
我模拟了你的数据，它正确地返回了 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'
回答by Justin
Query:
询问：
SELECT s.sno
FROM students s
WHERE NOT EXISTS (SELECT 0
FROM take t
WHERE t.sno = s.sno
AND t.cno = 'CS112')
回答by Slowcoder
Try this.
尝试这个。
SELECT s.sno FROM students s
LEFT JOIN take t ON t.sno = s.sno AND t.cno = 'CS112'
WHERE t.sno IS NULL;
