oracle 如何选择与同一个表中的相应行不匹配的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9488384/
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
How can I select rows that do not match corresponding rows in the same table?
提问by dee
I'm trying to figure out how to work out this query.
我想弄清楚如何解决这个查询。
I have a table with multiple columns. Of these columns there are two: colA and colB.
我有一个多列的表。这些列中有两列：colA 和 colB。
The values in colA and colB in a row will match a different row in the same table, however the actual values in those columns will not be equal.
一行中 colA 和 colB 中的值将匹配同一个表中的不同行，但这些列中的实际值将不相等。
So for any row:
所以对于任何行：
Col_A | Col_B
val1.0 | val2.0
there will be another row
会有另一排
Col_A | Col_B
val1.1| val2.1
There is an exception where this corresponding row does not exist and it is this row that I need to select.
有一个例外，这个对应的行不存在，我需要选择这一行。
Any help with the logic around this is greatly appreciated.
非常感谢有关此逻辑的任何帮助。
I've been working with queries of this type
我一直在处理这种类型的查询
Select *
from table1 e
where (e.Col_A = val1.0 and e.Col_B = val2.0)
and (e.Col_A != val1.1 and e.Col_B ! = val2.1)
is this even close? The above strikes me as I write it that if one clause is true then the other must also be true.
这甚至接近吗？当我写这篇文章时，上面的内容让我印象深刻，如果一个条款是真的，那么另一个也必须是真的。
Thanks in advance for any help or hints.
在此先感谢您的任何帮助或提示。
Additional info as there were requests for clarification.
其他信息，因为有澄清请求。
What I'm doing is updating a table. This table has a column that will catch and mark any exceptions to the rule I stated above.
我正在做的是更新表格。该表有一列将捕获并标记我上述规则的任何例外情况。
The table has about 12 columns and two of these columns can have one of two possible values. Value Types for these two columns are varchar2(30) Normally for any row that has a set of values there will be another row that has the other values. Some of the other unrelated columns can be the same but not necessarily.
该表有大约 12 列，其中两列可以具有两个可能值之一。这两列的值类型是 varchar2(30) 通常对于具有一组值的任何行，都会有另一行具有其他值。其他一些不相关的列可以相同，但不一定。
The code I have implemented is similar to below and is based on the left join suggestion from KAJ. However it seems to return too many rows in a straight query and in the update it actually updates every row.
我实现的代码与下面类似，并且基于 KAJ 的左连接建议。然而，它似乎在直接查询中返回了太多行，并且在更新中它实际上更新了每一行。
Update TableA Set Col_Ex = ('Exception')
Where Exists
(Select ... Example from Kaj s suggestion below);
回答by kaj
Assuming the following:
1. The values are fixed (i.e. 1.0 to 1.1 and 2.0 to 2.1)
2. What you're looking for is the original row where the corresponding row does not exist
3. The join between original and corresponding rows is in the columns you haven't mentioned (I've called it key below)
... then something like the following should work:
假设如下：
1. 值是固定的（即 1.0 到 1.1 和 2.0 到 2.1）
2. 您要查找的是对应行不存在的原始行
3. 原始行和对应行之间的连接在你没有提到的列（我在下面称之为关键）
......然后像下面这样的东西应该工作：
select original.*
from table original
left outer join table corresponding on corresponding.key = original.key and corresponding.Col_A = 'val1.1' and corresponding.Col_B = 'val2.1'
where original.Col_A = 'val1.0'
and original.Col_B = 'val2.0'
and corresponding.key is null
回答by Sam DeHaan
Select e.* from table e
where (e.Col_A = val1.0 and e.Col_B = val2.0)
and NOT EXISTS(SELECT * FROM table e2
WHERE e2.Col_A = val1.1
and e2.Col_B = val2.1 AND e2.key = e.key)
回答by Dan A.
I'm assuming that your values 1.0 and 1.1 are just one example of many. Otherwise there would not be multiple records to look for. Are you looking for values where A2 = (A1 + 0.1)? If so, you could use something like this:
我假设您的值 1.0 和 1.1 只是众多示例中的一个。否则不会有多个记录要查找。您是否正在寻找 A2 = (A1 + 0.1) 的值？如果是这样，你可以使用这样的东西：
select t1.col_a, t1.col_b
from table t1
left join table t2 on
(t2.col_a = t1.col_a + 0.1 and t2.col_b = t1.col_b + 0.1)
where
t2.col_a is null and t2.col_b is null
The LEFT JOIN will return null values when there is no record that meets the join criteria.
当没有符合连接条件的记录时，LEFT JOIN 将返回空值。
