BigQuery SQL排除不在空结果中 [英] 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.

改为使用not exists;

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和having:

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

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