防止 Oracle 减去语句删除重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/859255/
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
Prevent Oracle minus statement from removing duplicates
提问by Marcus Leon
Given these two tables:
鉴于这两个表：
CREATE TABLE TEST1 (TEST VARCHAR2(1 BYTE))
CREATE TABLE TEST2 (TEST VARCHAR2(1 BYTE))
Where TEST1 has two rows both with the value 'A' and TEST2 has one row with the value 'B'.
其中 TEST1 有两行的值为“A”，TEST2 有一行的值为“B”。
When I run this command:
当我运行此命令时：
SELECT TEST FROM TEST1
MINUS
SELECT TEST FROM TEST2
I get the output:
我得到输出：
Test
-----
A
It appears that MINUS removes the duplicates (as there are two 'A' rows in TEST1).
MINUS 似乎删除了重复项（因为 TEST1 中有两个“A”行）。
How can you get MINUS query to include duplicate values (return two 'A' rows)?
如何让 MINUS 查询包含重复值（返回两个“A”行）？
采纳答案by Andomar
Oracle supports multiple columns in the IN statement, so you can write:
Oracle 支持 IN 语句中的多列，因此您可以编写：
SELECT a, b, c
FROM table1
WHERE (a,b,c) not in (
select a,b,c from table2
)
回答by Dave Costa
Another option:
另外一个选项：
SELECT TEST, row_number() OVER (PARTITION BY TEST ORDER BY TEST) FROM TEST1
MINUS
SELECT TEST, row_number() OVER (PARTITION BY TEST ORDER BY TEST) FROM TEST2
This would be MINUS with each duplicate treated as a distinct entry. Note in the example below, if TEST1 has two 'C' values and TEST2 has only one, you get one in the output.
这将是 MINUS，每个重复项都被视为一个不同的条目。请注意，在下面的示例中，如果 TEST1 有两个“C”值而 TEST2 只有一个，则输出中会出现一个。
dev> select * from test1;
T
-
A
A
B
C
C
dev> select * from test2;
T
-
B
C
dev> SELECT TEST, row_number() OVER (PARTITION BY TEST ORDER BY TEST) FROM TEST1
2 MINUS
3 SELECT TEST, row_number() OVER (PARTITION BY TEST ORDER BY TEST) FROM TEST2
4 /
T ROW_NUMBER()OVER(PARTITIONBYTESTORDERBYTEST)
- --------------------------------------------
A 1
A 2
C 2
回答by Jeffrey Kemp
The "NOT IN" answers are all correct. An alternative, which might be easier for some scenarios, is the "NOT EXISTS" operator:
“NOT IN”的答案都是正确的。在某些情况下可能更容易的替代方法是“NOT EXISTS”运算符：
SELECT TEST FROM TEST1
WHERE NOT EXISTS
(SELECT null FROM TEST2 WHERE TEST2.TEST = TEST1.TEST);
(Note: the "null" in the select clause is meaningless here)
（注意：select 子句中的“null”在这里没有意义）
I personally use both methods, but I like the NOT EXISTS often because it is more flexible - it doesn't require the comparison to be on an equality condition, for example.
我个人使用这两种方法，但我经常喜欢 NOT EXISTS，因为它更灵活 - 例如，它不需要比较在相等条件下。
Recent versions of the optimiser will often convert a NOT IN to a NOT EXISTS, or vice versa; however, if you're on an older version (e.g. 8i or even 9i I think) you may see performance benefits from switching between these two methods.
优化器的最新版本通常会将 NOT IN 转换为 NOT EXISTS，反之亦然；但是，如果您使用的是旧版本（例如我认为是 8i 甚至 9i），您可能会发现在这两种方法之间切换会带来性能优势。
回答by cagcowboy
SELECT field1 FROM table1 WHERE field1 NOT IN (SELECT field2 FROM table2)
Will work so long as field2 can't contain NULLs.
只要 field2 不能包含 NULL 就可以工作。
回答by Kapil
SELECT TEST FROM TEST1 WHERE TEST IN(
SELECT TEST FROM TEST1
MINUS
SELECT TEST FROM TEST2);
回答by Shea
the ALL modifier makes UNION return all rows (e.g., UNION ALL), maybe it can be applied to MINUS? As in
ALL 修饰符使 UNION 返回所有行（例如，UNION ALL），也许它可以应用于 MINUS？如
select field1 from table1
minus all
select field2 from table2
回答by Equistatic
Part of what minus does is removes duplicates. Consider using NOT IN to avoid removing duplicates.
减号的部分作用是删除重复项。考虑使用 NOT IN 以避免删除重复项。
SELECT TEST FROM TEST1 WHERE TEST NOT IN(SELECT TEST FROM TEST2)
