具有与 Excel VLookup 相同功能的 SQL Server 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16472517/
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
SQL Server query with same functionality as Excel VLookup
提问by slayernoah
I have 2 columns of data that I need to compare with each other - Column A and Column B.
我有两列数据需要相互比较——A 列和 B 列。
Column A:
A栏：
Steve
Jane
Mary
Peter
Ed
Scott
Ted
Column B:
B栏：
Peter
Scott
David
Nancy
- Column A has a larger amount of data than column B.
- But it may not have all the values in column B.
- A 列的数据量大于 B 列的数据量。
- 但它可能没有 B 列中的所有值。
I need to find out which of the values in column B are also in column A.
我需要找出 B 列中的哪些值也在 A 列中。
Output expected for above sample data:
上述样本数据的预期输出：
Peter TRUE
Scott TRUE
David FALSE
Nancy FALSE
- Need to use SQL Server / T-SQL to get this output.
- Column A and Column B are fields in 2 separate tables
- There are no other columns in the 2 tables
- 需要使用 SQL Server / T-SQL 来获取此输出。
- A 列和 B 列是 2 个单独表中的字段
- 2个表中没有其他列
Thanks for all your help!
感谢你的帮助！
回答by ExactaBox
select
b.columnb,
case when a.columna is null then 'FALSE' else 'TRUE' end
from
tableb b left outer join
tablea a on b.columnb = a.columna
回答by Gordon Linoff
The problem with a left join is that there might be duplicates in table A.
左连接的问题在于表 A 中可能存在重复项。
If this is an issue, you can do this:
如果这是一个问题，您可以这样做：
select b.col, (case when a.val is NULL then 'FALSE' else 'TRUE' end)
from b left outer join
(select distinct a.val
from a
) a
on b.col = a.val;
An alternative way of expressing this is using a correlated subquery. This puts all the logic in the select:
另一种表达方式是使用相关子查询。这将所有逻辑放在select：
select b.col,
(case when exists (select 1 from a where a.val = b.col)
then 'TRUE'
else 'FALSE'
end)
from b
