google-sheets - 谷歌表格查询左连接一对多

Question

我有 2 个表，我正在尝试使用 Google 查询语言或任何可以输出结果集的公式执行左联接。

表1

表2

结果集

我怎样才能做到这一点？

问候

Answer 1

好的，这里是一个内部联接:

=ArrayFormula(query(iferror(split(flatten(if(transpose(filter(Table2!B2:B,Table2!B2:B<>""))=filter(Table1!A2:A,Table1!A2:A<>""),filter(Table1!A2:A,Table1!A2:A<>"")&"|"&transpose(filter(Table2!A2:A,Table2!A2:A<>"")),)),"|")),"select Col1,Col2 where Col1 is not null label Col1 '',Col2 ''"))

它构建一个 2D 数组并填充两组数据匹配的位置，然后将其展平回 1D 数组并将其拆分回两列。

我认为您只需添加不匹配的行即可获得左外连接:

=ArrayFormula({query(iferror(split(flatten(if(transpose(filter(Table2!B2:B,Table2!B2:B<>""))=filter(Table1!A2:A,Table1!A2:A<>""),
filter(Table1!A2:A,Table1!A2:A<>"")&"|"&transpose(filter(Table2!A2:A,Table2!A2:A<>"")),)),"|")),"select Col1,Col2 where Col1 is not null label Col1 '',Col2 ''");
filter(Table1!A2:B,isna(vlookup(Table1!A2:A,Table2!B2:B,1,false)))})

注意

这是一种特殊情况，其中第一个表仅由键 (ID) 组成，并且您只需要键加上第二个表中 ID 匹配的行的另一列。添加更多由管道符号(或选择的任何其他字符)分隔的列将是很简单的，但这些列必须进行硬编码:我不知道有什么方法可以使用这种方法自动包含来自两个列的所有列表。

这与答案 here 形成鲜明对比它会自动组合两个表中的列，但不允许建立一对多关系。