BigQuery SQL 排除 NOT IN 空结果 [英] BigQuery SQL Exclusion NOT IN empty results

问题描述

我遇到了不返回任何值的问题.数据库中有符合此条件的帐户.有点困惑为什么他们没有被退回.有什么建议吗?

I'm having problems with this not returning any values. There are accounts in the database that match this criteria. Somewhat confused why they aren't being returned. Any suggestions?

select accountid from `table1` 
where not in (select accountid from `table1` where action != "Action8")

推荐答案

不要使用 not in.从语义上讲，这是违反直觉的.如果子查询中的任何值为NULL，则不返回任何行.

Do not use not in. Semantically, it is counter-intuitive. If any values in the subquery are NULL, then no rows are returned.

改用不存在；

select t1.accountid
from `table1` t1
where not exists (select 1
                  from table1 tt1
                  where tt1.accountid  = t1.accountid and
                        tt1.action <> 'Action8'
                 );

或者使用group by和have:

select t1.accountid
from table1 t1
group by t1.accountid
having sum(case when action = 'Action8' then 1 else 0 end) = 0;

这篇关于BigQuery SQL 排除 NOT IN 空结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！