oracle 为两个表之间的公共行添加标志
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11502753/
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
adding flag for common rows between two tables
提问by user1356163
i have two tables say A and B. B is a subset of A. what i want to do is this : Add a flag column to table A(only for viewing, not permanently in the table) and the value of this flag should be yes for common rows between A and B and no for non common rows. For ex:
我有两个表说 A 和 B。B 是 A 的一个子集。我想要做的是：向表 A 添加一个标志列（仅用于查看，而不是永久在表中）并且该标志的值应该是是 A 和 B 之间的公共行，而不是非公共行。例如：
A table
Column1 Column2 Column3
X1 X2 X3
Y1 Y2 Y3
Z1 Z2 Z3
select * from A where column1=Y1; to get B
now my final output should be
现在我的最终输出应该是
Column1 Column2 Column3 FLAG
X1 X2 X3 NO
Y1 Y2 Y3 YES
Z1 Z2 Z3 NO
i have to everything below the code block in 1 sql statement(extracting B and adding flag). i am just able to extract B. unable to add flag
我必须在 1 个 sql 语句（提取 B 并添加标志）中代码块下方的所有内容。我只能提取 B. 无法添加标志
Using oracle 11.2.0.2.0,sqlplus
使用oracle 11.2.0.2.0,sqlplus
回答by APC
Use an outer join to conditionally link tables A and B, then use a CASE() statement to test whether a given row in A matches a row in B.
使用外连接有条件地链接表 A 和 B，然后使用 CASE() 语句测试 A 中的给定行是否与 B 中的行匹配。
select a.*
, case when b.column1 is not null then 'YES' else 'NO' end as flag
from a left outer join b
on a.column1 = b.column1
Note that this only works properly when there is just 0 or 1 instances of B.COLUMN1. If B contains multiple instances of any value of COLUMN1 then you can use this variant:
请注意，这仅在只有 0 或 1 个 B.COLUMN1 实例时才能正常工作。如果 B 包含 COLUMN1 的任何值的多个实例，那么您可以使用此变体：
select a.*
, case when b.column1 is not null then 'YES' else 'NO' end as flag
from a left outer join ( select distinct column1 from b ) b
on a.column1 = b.column1
回答by zmbq
You could try something like this:
你可以尝试这样的事情：
SELECT A.*,
CASE WHEN EXISTS
(SELECT Column1 FROM B WHERE Column1=A.Column1)
THEN "YES"
ELSE "NO"
END
FROM A
My PL-SQL is a bit rusty, example taken from here
我的 PL-SQL 有点生疏，示例取自此处
You can also do a LEFT JOIN on B, and see if B.Column1 is NULL or not.
您还可以在 B 上执行 LEFT JOIN，然后查看 B.Column1 是否为 NULL。
回答by OraNob
SELECT A.*, 'NO'
FROM A
WHERE NOT EXISTS
(SELECT 1 FROM B
WHERE B.COL1 = A.COL1
AND B.COL2 = A.COL2
AND B.COL3 = A.COL3) -- gets records only in A
UNION ALL
(SELECT B.*, 'YES') -- gets B records which are a subset of A
Since B is a subset of A - you already know these records should be tagged with a YES for your aliased column. The classical way of removing records from one recordset where they exist or don't exist in another recordset is of course using the EXISTS clause. The advantage of the EXISTS clause is it is a boolean operator and returns TRUE or FALSE to the call. And this return happens without the need for a full scan of the table - it is therefore faster (generally). You could also choose to use a MINUS clause, it might be more efficient. Try turning on the EXPLAIN PLAN.
由于 B 是 A 的子集 - 您已经知道这些记录应该为您的别名列标记为 YES。如果记录在另一个记录集中存在或不存在，则从一个记录集中删除记录的经典方法当然是使用 EXISTS 子句。EXISTS 子句的优点是它是一个布尔运算符，并向调用返回 TRUE 或 FALSE。并且这种返回不需要对表进行全面扫描 - 因此更快（通常）。您也可以选择使用 MINUS 子句，它可能更有效。尝试打开 EXPLAIN PLAN。
