Oracle SQL-根据条件进行计数，以包括零匹配的不同行

Question

是否存在"更好"；如何重构下面的查询，该查询为每个不同的id返回特定值(例如'A'(的出现次数？挑战似乎是保持id = 2在结果集中，即使计数为零(id = 2从不与'A'相关(。它有一个通用表表达式、NVL函数、内联视图、区别和左联接。完成这项工作真的需要这些吗？(Oracle 19c(

create table T (id, val) as
select 1, 'A' from dual
union all select 1, 'B' from dual
union all select 1, 'A' from dual
union all select 2, 'B' from dual
union all select 2, 'B' from dual
union all select 3, 'A' from dual
;
with C as (select id, val, count(*) cnt from T where val = 'A' group by id, val)
select D.id, nvl(C.cnt, 0) cnt_with_zero from (select distinct id from T) D left join C on D.id = C.id
order by id
;
ID CNT_WITH_ZERO
---------- -------------
1             2
2             0
3             1

Answer 1

一种简单的方法是条件聚合：

select id,
sum(case when val = 'A' then 1 else 0 end) as num_As
from t
group by id;

如果你有另一个表，每个id一行，我建议你：

select i.id,
(select count(*) from t where t.id = i.id and t.val = 'A') as num_As
from ids i;