谷歌表查询左联接一对多 [英] google sheets query left join one-to-many

问题描述

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

I have 2 tables and I am trying to perform a left join using google query language,or any formula that could output the result set.

表1

表2

结果集

我该怎么做?

致谢

推荐答案

好的，下面是一个内部连接，以以下内容开头:

OK well here is an inner join to start with:

=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数组并将其拆分为两列.

which builds up a 2D array and fills in the positions where the two sets of data match, then flattens it back into a 1D array and splits it back into two columns.

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

I think you just have to add the non-matching rows to get a left outer join:

=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匹配的行.添加更多由管道符号(或其他任何选择的字符)分隔的列将很容易，但是这些必须进行硬编码:我不知道这种方法会自动包含两个列中的所有列的任何方式桌子.

This is a special case where the first table just consists of keys (ID), and you want just the key plus the other column from the second table for rows where the IDs match. It would be straightforward to add more columns separated by a pipe symbol (or any other character of choice), but these would have to be hard-coded: I don't know of any way with this approach to automatically include all columns from both tables.

这与此处的答案相反.会自动合并两个表中的列，但不允许一对多关系.

This is in contrast to the answers here which do automatically combine columns from both tables but don't allow for a one-to-many relationship.

这篇关于谷歌表查询左联接一对多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！