如果我有一个模式,其中表具有可重复记录'age',并且该可重复记录的结构是:
{年龄:28}
是否可以发出查询来搜索行的'age'记录包含“age = 28 AND age = 56”的所有行。
所以在这个数据中:
[id = 1,年龄:[{年龄:28},{年龄:56},{年龄:62}]] [id = 2,年龄:[{年龄:28}]] [id = 3,年龄:[ {年龄:28},{年龄:56}]]
只有第1和第3行回来了吗?
提前致谢。
If I have a schema where a table has a repeatable record 'age', and the structure of that repeatable record is:
{age: 28}
Is it possible to issue a query to search for all rows where the row's 'age' record contains "age = 28 AND age = 56".
So in this data:
[id=1, age: [{age: 28},{age: 56},{age: 62}] ] [id=2, age: [{age: 28}] ] [id=3, age: [{age: 28},{age: 56}] ]
only rows 1 and 3 come back?
Thanks in advance.
最满意答案
假设记录中没有重复的年龄 和YourTable如下所示
id age 1 28 56 62 2 28 3 28 56for BigQuery Legacy SQL
SELECT * FROM YourTable OMIT RECORD IF SUM(age=28 OR age=56) <> 2要么
SELECT *, SUM(age=28 OR age=56) WITHIN RECORD AS qualify FROM YourTable HAVING qualify = 2for BigQuery Standard SQL(请参阅启用标准SQL )
SELECT * FROM YourTable WHERE (SELECT COUNT(1) FROM UNNEST(age) AS a WHERE a = 28 OR a = 56 ) = 2assuming there are no duplicate ages within the record and YourTable looks as below
id age 1 28 56 62 2 28 3 28 56for BigQuery Legacy SQL
SELECT * FROM YourTable OMIT RECORD IF SUM(age=28 OR age=56) <> 2or
SELECT *, SUM(age=28 OR age=56) WITHIN RECORD AS qualify FROM YourTable HAVING qualify = 2for BigQuery Standard SQL (see Enabling Standard SQL)
SELECT * FROM YourTable WHERE (SELECT COUNT(1) FROM UNNEST(age) AS a WHERE a = 28 OR a = 56 ) = 2更多推荐
发布评论