SQL Oracle 中的 EXCEPT 关键字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15445019/
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
EXCEPT keyword in Oracle
提问by 0x56794E
I'm trying to use the EXCEPT keyword in Oracle 10.1.0.2.0, but kept getting error 'Unknown Command'. I've tried googling around and someone said the keyword is MINUS, so I used MINUS, instead, but I still got the same error. Any idea? Thanks.
我正在尝试在 Oracle 10.1.0.2.0 中使用 EXCEPT 关键字，但一直收到错误“未知命令”。我试过谷歌搜索，有人说关键字是 MINUS，所以我改用了 MINUS，但我仍然遇到同样的错误。任何的想法？谢谢。
So here's my query. I'm finding the name of students who enrolls in ALL courses with course number > 500
所以这是我的查询。我正在查找注册所有课程且课程编号 > 500 的学生的姓名
SELECT s.name
FROM Students s
WHERE NOT EXISTS
(
SELECT c.id
FROM Courses c
WHERE c.number > 500
MINUS
SELECT e.course_id
FROM Enrollment e
WHERE e.student_id = s.id
);
回答by BellevueBob
Oracle MINUSis an operator; it's equivalent to EXCEPTin SQL Server. Here is a previous postexplaining the difference. Here's a trivial example:
OracleMINUS是一个运营商；它相当于EXCEPT在 SQL Server 中。 这是之前的帖子解释了差异。这是一个简单的例子：
SELECT a, b, c
FROM table_a
MINUS
SELECT a, b, c
FROM table_b
If you still have problems, add the complete query you are using to your question; it's likely a simple syntax error.
如果您仍有问题，请将您正在使用的完整查询添加到您的问题中；这可能是一个简单的语法错误。
回答by Lukasz Szozda
Oracle 20c will support EXCEPT/EXCEPT ALLkeywords.
Oracle 20c 将支持EXCEPT/EXCEPT ALL关键字。
SELECT col1, col2
FROM t1
EXCEPT
SELECT col1, col2
FROM t2;
or EXCEPT ALLif you want to handle duplicates:
或者EXCEPT ALL如果您想处理重复项：
SELECT col1, col2
FROM t1
EXCEPT ALL
SELECT col1, col2
FROM t2;
Set operators combine the results of two component queries into a single result.
EXCEPTAll distinct rows selected by the first query but not the second
EXCEPT ALLAll rows selected by the first query but not the second including duplicates
集合运算符将两个组件查询的结果合并为一个结果。
EXCEPT第一个查询但不是第二个查询选择的所有不同行
EXCEPT ALL第一个查询选择的所有行，但不是第二个，包括重复项
