tableb中的tablea中值数量？

Question

我有两个具有以下模式的表格：

CREATE TABLE tableA 
(
    accountNumber, --has colons in between values... need to be removed on join
    type, --(1,2,3,4,5,6,7,8,9,10)
    status1,
    status2
);
CREATE TABLE tableB 
(
    accountNumber,
    usage, -- [0,inf)
    day_id
);

我想知道TableB中没有多少个帐户名称。我已经构建了两种方法： 方式＃1

with cteA as (

Select upper(replace(accountNumber, ':',''))) as accountNumber,
       type
from tableA
where status1 = 'Active'
and status2 = 'Active'
and type in ('1','2','3','4')
),
cteB as (
Select distinct accountNumber 
from tableB
where usage > 0 
and day_id > '2022-01-01'
),
cte as (
Select a.accountNumber,
       b.accountNumber,
       a.type
from tableA a
left join tableB b 
on a.accountNumber = b.accountNumber
where b.accountNumber is null)
select type, count(*)
from cte
group by 1
order by 2 desc;  

＃2

select type,
       count(distinct accountNumber),
       count(distinct accountNumber) filter (where upper(replace(accountNumber,':','')) in (select accountNumber from tableB where day_id > '2022-01-01' and usage > 0))
from tableA
where status1 = 'Active'
and status2 = 'Active'
and type in ('1','2','3','4')
group by 1  

就我所知，这些应该给我相同的答案。当然，方法2要求我按下减法（在Excel或我的脑海中手动）...但是我不明白为什么这些查询会给我带来不同的价值吗？我想念什么吗？

Answer 1

如果我正确理解您的问题，那么应该有很多简单的方法。
怎么样：

select count(distinct accountNumber)
from tableA
where not exists(select 1 from tableB where tableA.accountNumber = tableB.accountNumber)