VLOOKUP 的 SQL 版本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24104707/
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 version of VLOOKUP
提问by user3715274
I am new to SQL and if you have a spare moment, I was wondering whether anybody could help me replicate the Excel Vlookupfunction in SQL please?
我是 SQL 新手，如果您有空闲时间，我想知道是否有人可以帮我Vlookup在 SQL 中复制 Excel函数？
From some research, I am suspecting that it is one of the joinfunctions that I require, however, I don't want to just select data that is contained in both tables - I just want to lookup the value in 1 table against another.
从一些研究中，我怀疑它是join我需要的功能之一，但是，我不想只选择两个表中包含的数据 - 我只想在一个表中查找另一个表中的值。
If the data is contained in the lookup table then return the value and if not, just return NULL.
如果数据包含在查找表中，则返回值，如果不包含，则返回NULL。
I have given a couple of example tables below to help illustrate my question.
我在下面给出了几个示例表来帮助说明我的问题。
Please note that Products 'C' and 'D' are not in Table2 but they are still in the result table but with NULL value.
请注意产品 'C' 和 'D' 不在表 2 中，但它们仍在结果表中，但值为 NULL。
Also I have a large number of unique products, so I am not looking for an answer which includes hard-coding, for example; CASE WHEN [Product] = 'A' THEN...
此外，我有大量独特的产品，所以我不是在寻找包含硬编码的答案，例如； CASE WHEN [Product] = 'A' THEN...
TABLE1
表格1
Product Quantity
-------------------
A 10
B 41
D 2
C 5
B 16
A 19
C 17
A 21
TABLE 2
表2
Product Cost
-----------------
A ￡31.45
B ￡97.23
RESULT TABLE
结果表
Product Quantity Cost
-----------------------------
A 10 ￡31.45
B 41 ￡97.23
D 2 NULL
C 5 NULL
B 16 ￡97.23
A 19 ￡31.45
C 17 NULL
A 21 ￡31.45
回答by Lennart
It looks as if you need an outer join, I'll use a left one in my example:
看起来好像你需要一个外连接，我将在我的例子中使用一个左连接：
select t1.Product, t1.Quantity, t2.Cost
from table1 as t1
left outer join table2 as t2
on t1.Product = t2.Product
You can also leave out the outer keyword:
您还可以省略 external 关键字：
select t1.Product, t1.Quantity, t2.Cost
from table1 as t1
left join table2 as t2
on t1.Product = t2.Product
回答by user890332
Here's an updated version of Lennart's answer which really works great.
这是 Lennart 答案的更新版本，确实非常有效。
select *
from table1 as t1
left outer join table2 as t2
on t1.Product = t2.Product
and t2.Product <> ''
left outer join table3 as t3
on t1.Product = t3.Product2
and t3.Product2 <> ''
The point is, you need to exclude rows where the join table column is blank, otherwise you will return way too many rows then table1 has. A true vlookup does not add any rows to the left table.
关键是，您需要排除连接表列为空的行，否则返回的行数将比 table1 多。真正的 vlookup 不会向左表添加任何行。
I even added a third table for effect.
我什至添加了第三张表格以达到效果。
