---not exists
select count(distinct a.pos_mer_id)
from tests a
where a.stat_flag='C' and not exists
(select 1 from
hfods.tests b
where b.stat_flag='N' and b.pos_mer_id=a.pos_mer_id);
改写成not in
select count(distinct a.pos_mer_id)
from tests awhere a.stat_flag='C' and a.pos_mer_id not in
(select b.pos_mer_id from
tests b
where b.stat_flag='N' and b.pos_mer_id is not null)