SQL Google 电子表格“=QUERY”join() 等效函数？

Google spreadsheet "=QUERY" join() equivalent function?

提问by jason

This question is concerning joining two databases in Google spreadsheet using =QUERY function

这个问题是关于使用 =QUERY 函数在 Google 电子表格中加入两个数据库

I have a table like so in range A1:C3

我在 A1:C3 范围内有一张像这样的桌子

a d g
b e h
c f i

I have another table

我还有一张桌子

c j m
a k n
b l o

I want the final table to look like this

我希望决赛桌看起来像这样

a d g k n
b e h l o 
c f i j m

I can do this by using a vlookup function pretty easily in cell D1 and paste it down and across, but my dataset is huge. I would need a whole page of vlookups and Google Spreadsheet tells I'm at my limit in complexities.

我可以通过在单元格 D1 中使用 vlookup 函数很容易地做到这一点，并将其粘贴下来，但我的数据集很大。我需要一整页的 vlookups，而 Google 电子表格告诉我在复杂性方面已达到极限。

I look at the Google's Query Language reference... there doesn't seem to be an type of "join" functions mentioned. You would think it would be an easy "join on A" type operation.

我查看了 Google 的 Query Language 参考资料......似乎没有提到一种“加入”功能。您会认为这是一个简单的“在 A 上加入”类型的操作。

Can anybody solves this without a vlookup?

有人可以在没有 vlookup 的情况下解决这个问题吗？

采纳答案by Rubén

Short answer

简答

Google QUERY Languageversion 0.7 (2016) doesn't include a JOIN (LEFT JOIN) operator but this could be achieved by using an array formula which result could be used as input for the QUERY function or for other uses.

Google QUERY 语言版本 0.7 (2016) 不包含 JOIN (LEFT JOIN) 运算符，但这可以通过使用数组公式来实现，其结果可用作 QUERY 函数的输入或用于其他用途。

Explanation

解释

Array formulas and the array handling features of Google Sheetsmake possible to make a JOIN between two simple tables. In order to make easier to read, the proposed formula use named ranges instead of range references.

数组公式和Google Sheets的数组处理功能可以在两个简单的表之间进行 JOIN。为了更容易阅读，建议的公式使用命名范围而不是范围引用。

Named Ranges

命名范围

• table1 : Sheet1!A1:C3
• table2 : Sheet2!A1:C3
• ID : Sheet1!A1:A3

• 表1：表1！A1：C3
• 表 2 : Sheet2!A1:C3
• ID : Sheet1!A1:A3

Formula

公式

=ArrayFormula(
   {
     table1,
     vlookup(ID,table2,COLUMN(Indirect("R1C2:R1C"&COLUMNS(table2),0)),0)
   }
)

Remarks:

评论：

• Using open ended ranges is possible but this could make the the spreadsheet slower.
• To speed up the recalculation time :
  1. Replace Indirect("R1C2:R1C"&COLUMNS(table2),0)by an array of constants from 2 to number of columns of table2.
  2. Remove the empty rows from the spreadsheet

• 使用开放式范围是可能的，但这可能会使电子表格变慢。
• 加快重新计算时间：
  1. 替换Indirect("R1C2:R1C"&COLUMNS(table2),0)为从 2 到 table2 的列数的常量数组。
  2. 从电子表格中删除空行

Example

例子

See this sheetfor an example

有关示例，请参阅此表

Note

笔记

On 2017 Google improved the official help article in English about QUERY, QUERY function. It still doesn't include yet topics like this but could be helpful to understand how it works.

2017年Google完善了英文官方帮助文章关于QUERY、QUERY函数。它仍然不包括这样的主题，但可能有助于理解它是如何工作的。

回答by White_King

You can use ARRAYFORMULAor YOU can just drag this formula: after an import or QUERY-ing the first table; in the D column:

您可以使用ARRAYFORMULA或您可以拖动此公式：在导入或QUERY-ing 第一个表之后；在 D 栏中：

=QUERY(Sheet2!A1:C3, "Select B,C WHERE A='" & A1 & "'", 0)

回答by Viktor

So, this answers how you do it WITH a Vlookup-function, but in only one cell.
In your example, given that each table of data has the following cell references:

所以，这回答了你如何使用 Vlookup 函数来做这件事，但只在一个单元格中。
在您的示例中，假设每个数据表都有以下单元格引用：

Table1: Sheet1!A1:C3

表 1: Sheet1!A1:C3

a d g
b e h
c f i

Table2: Sheet2!A1:C3

表 2: Sheet2!A1:C3

c j m
a k n
b l o

This is how the formula should be constructed.

应该这样构造公式。

Join-formula

连接公式

=ArrayFormula(
   {
     Sheet1!A1:C,
     vlookup(Sheet1!A1:A, {Sheet2!A1:A, Sheet2!B1:C}, {2,3}, false)
   }
)

The key to get this formula to work, is to understand how to use curly brackets in the Vlookup Range. You basically define the first cell reference of the Range as the column which is to be a match to the Vlookup Search_Key. The rest of the cell references in the Range is in relation to the columns which you would like to join.

使此公式起作用的关键是了解如何在 Vlookup 范围中使用大括号。您基本上将范围的第一个单元格引用定义为与 Vlookup Search_Key 匹配的列。范围中的其余单元格引用与您要加入的列有关。

The Index is written as {2,3} to return the second and third column of the Range (the Range consists of a total of 3 columns); curly brackets has nothing to do with Arrayformula in the Vlookup Index, but is necessary to return multiple columns from the Vlookup function. The reason to not write {1,2,3} is because you would not like to include the column which is being used for the purpose of joining.

Index写成{2,3}，返回Range的第二、第三列（Range一共3列）；大括号与 Vlookup 索引中的 Arrayformula 无关，但需要从 Vlookup 函数返回多列。不写 {1,2,3} 的原因是因为您不想包含用于加入目的的列。

Example where the column in table2 used for joining, is located in a different column (to the right of the data which is to be joined)

用于连接的 table2 中的列位于不同列中的示例（在要连接的数据的右侧）

This kind of Join-formula can be utilized even if the join-column in the second table is located as the third column of that table. Let's say that the raw-data in this example would look like this:

即使第二个表中的连接列位于该表的第三列，也可以使用这种连接公式。假设本示例中的原始数据如下所示：

Table1 (Sheet1):

表1（表1）：

a d g
b e h
c f i

Table2 (Sheet2):

表2（表2）：

j m c
k n a
l o b

If you write the formula like this, you'll still get the desired outcome (as displayed in the table of joined data):

如果您像这样编写公式，您仍然会得到所需的结果（如连接数据表中所示）：

=ArrayFormula(
   {
     Sheet1!A1:C,
     vlookup(Sheet1!A1:A, {Sheet2!C1:C, Sheet2!A1:B}, {2,3}, false)
   }
)

The table of joined data:

连接数据表：

a d g k n
b e h l o 
c f i j m

In the Join-formula, notice that the third column of Table2 is located as the first cell reference in the Vlookup Range!
The reason to why this works, is because when you use curly brackets in the Range (in conjunction with Arrayformula), the Vlookup Search_Key will NOT look for a column as a common denominator within the raw-data, instead it will use the Array within curly brackets as a reference to find a column as a common denominator (by default this is the first column of the Range).

在 Join-formula 中，请注意 Table2 的第三列位于 Vlookup Range 中的第一个单元格引用！
这之所以有效，是因为当您在范围中使用大括号（与 Arrayformula 结合使用）时，Vlookup Search_Key 不会在原始数据中查找作为公分母的列，而是会使用其中的 Array大括号作为参考以查找作为公分母的列（默认情况下，这是范围的第一列）。

I've written a comprehensive guide about this topic called:

我写了一个关于这个主题的综合指南，名为：

'Mastering Join-formulas in Google Sheets'

“掌握 Google 表格中的联接公式”

回答by Marco Roy

If you can map each "index" (a, b, c) to a specific row or column, then you could use the INDEXfunction.

如果您可以将每个“索引”（a、b、c）映射到特定的行或列，那么您就可以使用该INDEX函数。

In this case, you could probably map 'a' to column A (or row 1), 'b' to column B (or row 2), and so on.

在这种情况下，您可以将“a”映射到 A 列（或第 1 行），将“b”映射到 B 列（或第 2 行），依此类推。

Also, Merge Tablesseem to address this exact use case.

此外，合并表似乎解决了这个确切的用例。

回答by pnuts

With the 'other' table in A5:C7, please try:

使用 A5:C7 中的“其他”表，请尝试：

=query({A1:C3,query(sort(A5:C7,1,TRUE),"Select Col2,Col3")})

回答by user2738245

I solved this by using Javascript LINQ (language integrated query).

我通过使用 Javascript LINQ（语言集成查询）解决了这个问题。

It lets you specify Javascript with complex join conditions. You can also perform other SQL queries such as Grouping, Projecting, Sorting and Filtering your sheets as if they were database tables. Look at the links below.

它允许您指定具有复杂连接条件的 Javascript。您还可以执行其他 SQL 查询，例如分组、投影、排序和过滤您的工作表，就像它们是数据库表一样。看看下面的链接。

Note that in the LINQ query language I replaced all spaces in column names with underscores to make them valid JS identifiers.

请注意，在 LINQ 查询语言中，我用下划线替换了列名中的所有空格，使它们成为有效的 JS 标识符。

https://docs.google.com/spreadsheets/d/1DHtQlQUlo-X_YVfo-Wo-b7315sSk2pxL5ci4Y9lxvZo/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1DHtQlQUlo-X_YVfo-Wo-b7315sSk2pxL5ci4Y9lxvZo/edit?usp=sharing

https://script.google.com/d/1R5L2ReHJrBRwyoSoVOFLzEQZiGtxidPfPkAeVownt7SWX6TpacY7gA7j/edit?usp=sharing

https://script.google.com/d/1R5L2ReHJrBRwyoSoVOFLzEQZiGtxidPfPkAeVownt7SWX6TpacY7gA7j/edit?usp=sharing