google-sheets - 如何在QUERY中使用CONCAT？

Question

我有一张 table :

 A     |   B      |     C
BEN    |  MOSKOW  |    YES
ANTON  |  IRKUTSK |     NO
VIKTOR |  PARIS   |    YES
BEN    |  PARIS   |    YES
ANTON  |  TORONTO |     NO
DON    | TORONTO  |    YES
ANNA   | IRKUTSK  |    YES
BEN    |  MOSKOW  |     NO

并尝试了一个公式:

=UNIQUE(query(A:C; "Select A,B,C where A contains 'BEN' OR A contains 'ANTON' order by A"))

和一个arrayformula:

=arrayformula(UNIQUE(query(A:C; "Select A,B,C where A contains 'BEN' OR A contains 'ANTON' order by A")))

但结果在列中:

ANTON  |  IRKUTSK |     NO
ANTON  |  TORONTO |     NO
BEN    |  MOSKOW  |    YES
BEN    |  PARIS   |    YES
BEN    |  MOSKOW  |     NO

而我只需要每行只有一个单元格的结果，就像这样:

ANTON IRKUTSK NO
ANTON TORONTO NO
BEN MOSCOW YES
BEN PARIS YES
BEN MOSKOW NO

Answer 1

Google表格concat中没有query选项。

JPV建议的解决方法可能适合您:
https://stackoverflow.com/a/29799384/5372400

使用公式:
=ARRAYFORMULA(
SUBSTITUTE(
trim(transpose(query(transpose(_your_query_here_),,COLUMNS(_your_query_here_))))," "," → ")
)
将" → "更改为空格" "以将结果与空格合并。

有关表格中SQL的更多信息:

https://developers.google.com/chart/interactive/docs/querylanguage