您现有的查询不起作用，因为email的值不能同时为同一行中的“email 1”和“email 2”。您要寻找的是所谓的关系划分，可以使用条件聚合来实现。但根据分组，这将导致2（客户收到这些电子邮件组合）。基于窗口聚合的相同逻辑返回行数：

with cte as 
 ( select t.*
     -- flag rows matching a single mail
     ,max(case when email = 'Email 1' then 1 else 0 end) over (partition by cust_id) as flag_1
     ,max(case when email = 'Email 2' then 1 else 0 end) over (partition by cust_id) as flag_2
     ,max(case when email = 'Email 3' then 1 else 0 end) over (partition by cust_id) as flag_3
   from WORK_TBLS_LOADS.aw_fpq_ajm_current AS t
   -- filter emails to be matched
   where email in ('Email 1','Email 2','Email 3')
   -- filter for customers with combinations of emails
   qualify flag_1 + flag_2 = 2
        or flag_2 + flag_3 = 2
        or flag_1 + flag_3 = 2
 )
select count(*)
from cte
;

编辑，根据您的评论，如果我想要一个不同的客户计数，我会：：选择计数（不同的客户id）？
是的，这是可行的，但与传统的分组方法相比，它的开销更大。相同的逻辑，但在cte中为每个匹配的客户返回一行：

with cte as 
 ( select cust_id
     ,max(case when email = 'Email 1' then 1 else 0 end) as flag_1
     ,max(case when email = 'Email 2' then 1 else 0 end) as flag_2
   from WORK_TBLS_LOADS.aw_fpq_ajm_current AS t
   where email in ('Email 1','Email 2')
   group by 1
   having flag_1 + flag_2 = 2
 )
select count(*)
from cte
;

你似乎想统计电子邮件，而不是客户：

select count(*)
from t
where exists (select 1 from t t2 where t2.custid = t.custid and email = 'Email 1') and
      exists (select 1 from t t2 where t2.custid = t.custid and email = 'Email 2') ;

你说你想计算客户数，但似乎你真的想以某种方式计算行数。说到底，条件聚合就是您要寻找的。像这样的

with cte as
-- conditional aggregation by Cust_ID 
 ( select Cust_ID,
     ,sum(case when email = 'Email 1' then 1 else 0 end) as email_1_count
     ,sum(case when email = 'Email 2' then 1 else 0 end) as email_2_count
     ,sum(case when email = 'Email 3' then 1 else 0 end) as email_3_count
   from WORK_TBLS_LOADS.aw_fpq_ajm_current
   group by Cust_ID
 )
-- summarize to just total counts
select count(*) as cust_count, sum(email_1_count+email_2_count) as row_count
from cte
where email_1_count > 0 and email_2_count > 0
;

我不知道一般情况。您可以通过添加更多 union all 查询。
如果您的“匹配集”有任何重叠，这就有可能重复计算。这个想法主要是一种简单的方法，将单个查询的结果与可以简单相加的假设结合起来。很难确定你的大局要求是什么。

with data as (

    select count(*) as c
    from WORK_TBLS_LOADS.aw_fpq_ajm_current
    where Email in ('Email 1' ,'Email 2')
    group by Cust_Id
    having count(distinct Email) = 2 -- because there are two emails in this match

    union all

    select count(*) as c
    from WORK_TBLS_LOADS.aw_fpq_ajm_current
    where Email in ('Email 2' ,'Email 3')
    group by Cust_Id
    having count(distinct Email) = 2

)
select sum(c) from data

这会让你们两个——客户数和电子邮件数。其工作方式是——我们只保留那些共享这两个电子邮件ID的客户。然后我们聚合。

select count(distinct id) as num_customer, 
       count(*) as num_emails
from t a
where exists (select 1 from t b
              where b.id=a.id
              and email in ('Email 1', 'Email 2') 
              group by id
              having count(distinct email)=2);

演示