防止Oracle减号语句删除重复项 [英] Prevent Oracle minus statement from removing duplicates

问题描述

给出以下两个表:

CREATE TABLE TEST1 (TEST  VARCHAR2(1 BYTE))
CREATE TABLE TEST2 (TEST  VARCHAR2(1 BYTE))

其中TEST1具有两行，两者均为'A'，而TEST2具有一行，其值为'B'.

Where TEST1 has two rows both with the value 'A' and TEST2 has one row with the value 'B'.

当我运行此命令时:

SELECT TEST FROM TEST1
MINUS
SELECT TEST FROM TEST2

我得到输出:

Test
-----
A

似乎MINUS删除了重复项(因为TEST1中有两个"A"行).

It appears that MINUS removes the duplicates (as there are two 'A' rows in TEST1).

如何获取MINUS查询以包含重复值(返回两个"A"行)?

How can you get MINUS query to include duplicate values (return two 'A' rows)?

推荐答案

Oracle在IN语句中支持多个列，因此您可以编写:

Oracle supports multiple columns in the IN statement, so you can write:

SELECT a, b, c
FROM table1
WHERE (a,b,c) not in (
    select a,b,c from table2
)

这篇关于防止Oracle减号语句删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！