英文:
SQL Syntax WHERE NOT IN for Google BigQuery
问题
我收到了错误信息：
类型为IN的子查询必须只有一个输出列 [2:39]
当我的查询是：
从表中选择* WHERE（id_loan，aud_insert_ts）不在（选择id_loan，MAX（aud_insert_ts）
从表中
按id_loan分组）
有任何想法吗？不确定Google SQL是否支持NOT IN。
谢谢
请帮我
谢谢
Manuel
英文:
I get the error:
Subquery of type IN must have only one output column at [2:39]
when my query is:
select * FROM table
WHERE (id_loan,aud_insert_ts) NOT IN (SELECT id_loan, MAX(aud_insert_ts)
FROM table
GROUP BY id_loan)
Any ideas? Not sure if Google SQL supports NOT IN.
Thanks
help me, please
thanks
Manuel
答案1
得分: 0
错误消息并不受您查询中使用的 NOT IN 影响。问题在于，根据错误消息，您试图检索多于一个列：
SELECT id_loan, MAX(aud_insert_ts)
您可以简化它如下：
SELECT *
FROM table t1
WHERE NOT EXISTS (
SELECT 1
FROM table t2
WHERE t1.id_loan = t2.id_loan AND
GROUP BY t1.aud_insert_ts
HAVING MAX(t2.aud_insert_ts) = t1.aud_insert_ts
)
基本上，我们通过 NOT EXISTS 进行筛选，因为我们实际上并不关心内部查询的结果，我们只关心它们是否存在。因此，在内部查询中，我们过滤掉了任何与外部 t1 的相应字段不匹配的内容，因此子查询计算的记录只有匹配的贷款。
现在，我们按 t1.aud_insert_ts 分组，并查找 t2 中的最大值进行比较。如果它们匹配，那么我们将获得单一记录的结果，因此外部查询将过滤掉 t1，而如果这是不匹配的，那么外部查询中的 NOT EXISTS 条件将允许 t1 进入结果。
英文:
The error message is not troubled by the use of NOT IN in your query. The problem is, according to the error message that you attempt to retrieve more than one column at
SELECT id_loan, MAX(aud_insert_ts)
You could simplify it like this:
SELECT *
FROM table t1
WHERE NOT EXISTS (
SELECT 1
FROM table t2
WHERE t1.id_loan = t2.id_loan AND
GROUP BY t1.aud_insert_ts
HAVING MAX(t2.aud_insert_ts) = t1.aud_insert_ts
)
Basically, we filter by NOT EXISTS, as we are not really interested to see the results in the inner query, we are only interested in their lack of existence. So, in the inner query we filter out anything whose id_loan is mismatching the outer t1's corresponding field, so the records that end up being taken account by the sub-query are only the matching loans.
Now, we group by t1.aud_insert_ts and find the maximum value in t2 to compare this field to. If they match, then we will have a single-record result, so the outer query will filter out t1, whereas if this is a mismatch, then the NOT EXIST criteria in the outer query will let in t1 into the result.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论