用WHERE子句的值范围查询？ [英] Query with range of values for WHERE clause?
问题描述
我有Google电子表格，我想运行 QUERY 功能。但我希望 WHERE 语句检查一系列值。我基本上在寻找我将在SQL中使用 IN 语句的内容 - Google电子表格中的 IN 等效项是什么？所以现在我有：
= QUERY（Sheet1！A3：AB50，Select A，B，AB WHERE B =' & G4&'）
但我真正需要的是相当于：
= QUERY（Sheet1！A3：AB50，Select A，B，AB WHERE B =''& G4：G7&''）
当然，陈述失败。我怎样才能得到哪些地方抵制一系列的价值？这些都是文本值，如果这有所作为。
虽然我现在不是完美的答案，但我可以找到一个解决问题的办法，可以在少量的数据中使用（希望这里的情况:)）第一步：你应该创建一个帮助单元格 您将G4：G7单元与|连接起来字符：
= CONCATENATE（G3，|，G4，|，G5，|，G6，|，G7） - 假设它是单元格H2的内容。
现在，您应该更改上述查询，如下所示：
= QUERY （Sheet1！A3：AB50，Select A，B，AB WHERE B matches'^。*（& H2&）。* $'）
这应该可以做到。基本上我们匹配运算符允许使用正则表达式，这也允许使用|进行构造符号。
正如我所说，这是一种解决方法，它有缺点，但我希望它会有帮助。
I have a Google Spreadsheet and I want to run a QUERY function. But I want the WHERE statement to check a series of values. I'm basically looking for what I would use an IN statement in SQL - what the IN equivalent in Google Spreadsheets? So right now I have:
=QUERY(Sheet1!A3:AB50,"Select A,B, AB WHERE B='"& G4 &"'")
And that works. But what I really need is the equivalent of:
=QUERY(Sheet1!A3:AB50,"Select A,B, AB WHERE B='"& G4:G7 &"'")
And of course, that statement fails. How can I get the where against a range of values? These are text values, if that makes a difference.
Although I don't now the perfect answer for that, I could find a workaround for the problem, which can be used in small amount of data (hope that's the case here :) )
First step: You should create a "helper cell" in which you concatenate the G4:G7 cells with a "|" character:
=CONCATENATE(G3,"|",G4,"|",G5,"|",G6,"|",G7) - Let's say it's the content of the cell H2.
Now, you should change your above query as follows:
=QUERY(Sheet1!A3:AB50,"Select A,B, AB WHERE B matches '^.*(" & H2 & ").*$'")
This should do the trick. Basically we the "matches" operator allows the use of regular expressions, which also allow construction using the | symbol.
As I said, it's a workaround, it has drawbacks, but I hope it'll help.
这篇关于用WHERE子句的值范围查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
