Excel Jet Access 查询，模拟 vlookup

Question

我正在尝试将以下逻辑合并到我的查询中，这有点像 2 个 vlookups，如果其中一个不可用，则使用下一个。

=IFERROR(VLOOKUP(D2,'分割'!$A:$C,3,0),IFERROR(VLOOKUP(C2,'分割'!$A:$C,3,0),"“))

所以它应该首先查找 D 列(unit2)，然后如果没有查找 C 列(值)

这是我到目前为止所拥有的，我尝试过OR连接:

SELECT DISTINCT A.[unit], B.[Hierarchy] FROM [Mapping$] as A

inner join [Segmentation$] as B

ON (A.[unit2] = B.[Number] OR
      A.[Value] = B.[Number])

WHERE B.[Hierarchy] <> ''

Order By A.[unit] asc

还尝试过:

SELECT DISTINCT A.[unit], B.[Hierarchy] FROM [Mapping$] as A

inner join [Segmentation$] as B

ON Nz(A.[unit2], A.[Value])=B.[Number]

WHERE B.[Hierarchy] <> ''

Order By A.[unit] asc

分割表中的示例数据:

+--------------+-------------+-----------+
| Number       |  Name       | Hierarchy |
+--------------+-------------+-----------+
|        11773 | Stack       | Overflow  |
+--------------+-------------+-----------+

映射表:

+-------+-----------+-------+-------+-----------+---------+
| unit  | variable1 | Value | unit2 | variable2 | value2  |
+-------+-----------+-------+-------+-----------+---------+
| 11773 | lvl0      | 80000 | 11773 | lvl0_nm   | thing 1 |
| 11773 | lvl1      | 89000 | 11773 | lvl1_nm   | thing 2 |
| 11773 | lvl2      | 89022 | 11773 | lvl2_nm   | thing 3 |
| 11773 | lvl3      | 89756 | 11773 | lvl3_nm   | thing 4 |
| 11773 | lvl4      | 89833 | 11773 | lvl4_nm   | thing 5 |
| 11773 | lvl5      | 11773 | 11773 | lvl5_nm   |         |
| 11773 | lvl6      |       | 11773 | lvl6_nm   |         |
| 11773 | lvl7      |       | 11773 | lvl7_nm   |         |
| 11773 | lvl8      |       | 11773 | lvl8_nm   |         |
| 11773 | lvl9      |       | 11773 | lvl9_nm   |         |
| 11773 | lvl10     |       | 11773 | lvl10_nm  |         |
| 11773 | lvl11     |       | 11773 | lvl11_nm  |         |
| 11773 | lvl12     |       | 11773 | lvl12_nm  |         |
| 11773 | lvl13     |       | 11773 | lvl13_nm  |         |
| 11773 | lvl14     |       | 11773 | lvl14_nm  |         |
| 11773 | lvl15     |       | 11773 | lvl15_nm  |         |
| 11773 | lvl16     |       | 11773 | lvl16_nm  |         |
| 11773 | lvl17     |       | 11773 | lvl17_nm  |         |
| 11773 | lvl18     |       | 11773 | lvl18_nm  |         |
+-------+-----------+-------+-------+-----------+---------+

预期结果:

+-------+-----------+-------+-------+-----------+---------+-----------+
| unit  | variable1 | Value | unit2 | variable2 | value2  | Hierarchy |
+-------+-----------+-------+-------+-----------+---------+-----------+
| 11773 | lvl0      | 80000 | 11773 | lvl0_nm   | thing 1 | Overflow  |
| 11773 | lvl1      | 89000 | 11773 | lvl1_nm   | thing 2 | Overflow  |
| 11773 | lvl2      | 89022 | 11773 | lvl2_nm   | thing 3 | Overflow  |
| 11773 | lvl3      | 89756 | 11773 | lvl3_nm   | thing 4 | Overflow  |
| 11773 | lvl4      | 89833 | 11773 | lvl4_nm   | thing 5 | Overflow  |
| 11773 | lvl5      | 11773 | 11773 | lvl5_nm   |         | Overflow  |
| 11773 | lvl6      |       | 11773 | lvl6_nm   |         | Overflow  |
| 11773 | lvl7      |       | 11773 | lvl7_nm   |         | Overflow  |
| 11773 | lvl8      |       | 11773 | lvl8_nm   |         | Overflow  |
| 11773 | lvl9      |       | 11773 | lvl9_nm   |         | Overflow  |
| 11773 | lvl10     |       | 11773 | lvl10_nm  |         | Overflow  |
| 11773 | lvl11     |       | 11773 | lvl11_nm  |         | Overflow  |
| 11773 | lvl12     |       | 11773 | lvl12_nm  |         | Overflow  |
| 11773 | lvl13     |       | 11773 | lvl13_nm  |         | Overflow  |
| 11773 | lvl14     |       | 11773 | lvl14_nm  |         | Overflow  |
| 11773 | lvl15     |       | 11773 | lvl15_nm  |         | Overflow  |
| 11773 | lvl16     |       | 11773 | lvl16_nm  |         | Overflow  |
| 11773 | lvl17     |       | 11773 | lvl17_nm  |         | Overflow  |
| 11773 | lvl18     |       | 11773 | lvl18_nm  |         | Overflow  |
+-------+-----------+-------+-------+-----------+---------+-----------+

第一次查找失败时的示例:

+--------+-------+-----------+
| Number | Name  | Hierarchy |
+--------+-------+-----------+
|  82282 | Super | User      |
+--------+-------+-----------+

映射表

+-------+-----------+-------+-------+-----------+----------+
| unit  | variable1 | Value | unit2 | variable2 |  value2  |
+-------+-----------+-------+-------+-----------+----------+
| 96702 | lvl0      | 80000 | 96702 | lvl0_nm   | thing 6  |
| 96702 | lvl1      | 80393 | 96702 | lvl1_nm   | thing 7  |
| 96702 | lvl2      | 85176 | 96702 | lvl2_nm   | thing 8  |
| 96702 | lvl3      | 82282 | 96702 | lvl3_nm   | thing 9  |
| 96702 | lvl4      | 87927 | 96702 | lvl4_nm   | thing 10 |
| 96702 | lvl5      | 88172 | 96702 | lvl5_nm   | thing 11 |
| 96702 | lvl6      | 81025 | 96702 | lvl6_nm   | thing 12 |
| 96702 | lvl7      | 96702 | 96702 | lvl7_nm   |          |
| 96702 | lvl8      |       | 96702 | lvl8_nm   |          |
| 96702 | lvl9      |       | 96702 | lvl9_nm   |          |
| 96702 | lvl10     |       | 96702 | lvl10_nm  |          |
| 96702 | lvl11     |       | 96702 | lvl11_nm  |          |
| 96702 | lvl12     |       | 96702 | lvl12_nm  |          |
| 96702 | lvl13     |       | 96702 | lvl13_nm  |          |
| 96702 | lvl14     |       | 96702 | lvl14_nm  |          |
| 96702 | lvl15     |       | 96702 | lvl15_nm  |          |
| 96702 | lvl16     |       | 96702 | lvl16_nm  |          |
| 96702 | lvl17     |       | 96702 | lvl17_nm  |          |
| 96702 | lvl18     |       | 96702 | lvl18_nm  |          |
+-------+-----------+-------+-------+-----------+----------+

预期结果:

+-------+-----------+-------+-------+-----------+----------+-----------+
| unit  | variable1 | Value | unit2 | variable2 |  value2  | Hierarchy |
+-------+-----------+-------+-------+-----------+----------+-----------+
| 96702 | lvl0      | 80000 | 96702 | lvl0_nm   | thing 6  |           |
| 96702 | lvl1      | 80393 | 96702 | lvl1_nm   | thing 7  |           |
| 96702 | lvl2      | 85176 | 96702 | lvl2_nm   | thing 8  |           |
| 96702 | lvl3      | 82282 | 96702 | lvl3_nm   | thing 9  | User      |
| 96702 | lvl4      | 87927 | 96702 | lvl4_nm   | thing 10 |           |
| 96702 | lvl5      | 88172 | 96702 | lvl5_nm   | thing 11 |           |
| 96702 | lvl6      | 81025 | 96702 | lvl6_nm   | thing 12 |           |
| 96702 | lvl7      | 96702 | 96702 | lvl7_nm   |          |           |
| 96702 | lvl8      |       | 96702 | lvl8_nm   |          |           |
| 96702 | lvl9      |       | 96702 | lvl9_nm   |          |           |
| 96702 | lvl10     |       | 96702 | lvl10_nm  |          |           |
| 96702 | lvl11     |       | 96702 | lvl11_nm  |          |           |
| 96702 | lvl12     |       | 96702 | lvl12_nm  |          |           |
| 96702 | lvl13     |       | 96702 | lvl13_nm  |          |           |
| 96702 | lvl14     |       | 96702 | lvl14_nm  |          |           |
| 96702 | lvl15     |       | 96702 | lvl15_nm  |          |           |
| 96702 | lvl16     |       | 96702 | lvl16_nm  |          |           |
| 96702 | lvl17     |       | 96702 | lvl17_nm  |          |           |
| 96702 | lvl18     |       | 96702 | lvl18_nm  |          |           |
+-------+-----------+-------+-------+-----------+----------+-----------+

Answer 1

你的代码，

SELECT 
    DISTINCT A.[unit],
    B.[Hierarchy] 
FROM [Mapping$] as A inner join [Segmentation$] as B ON (A.[unit2] = B.[Number] OR A.[Value] = B.[Number])
WHERE B.[Hierarchy] <> ''    
Order By A.[unit] asc;

由于您正在使用Inner Join你不需要WHERE B.[Hierarchy] <> '' 。仅当存在匹配时，连接才会产生结果。

要获得预期结果，您可以使用 LEFT JOIN或子查询。

SELECT 
    DISTINCT A.[unit],
    B.[Hierarchy] 
FROM [Mapping$] as A **LEFT JOIN** [Segmentation$] as B ON (A.[unit2] = B.[Number] OR A.[Value] = B.[Number])
Order By A.[unit] asc;

这将生成唯一 A.Unit 的列表和B.Hierarchy如果有的话。

您还可以使用子查询。 (如果 segmentation.Number 不唯一，使用 Top 1。否则您的子查询将返回多行并提示)

SELECT 
    A.*,
    (SELECT B.[Hierarchy] 
     FROM [Segmentation$] as B
     WHERE (A.[unit2] = B.[Number] OR A.[Value] = B.[Number])) AS Hierarchy
FROM [Mapping$] as A
Order By A.[unit] asc;