select to_char(d.date1,'YYYY') , count(*) 
from Table1 a, Table2 b, Table3 c, Table4 d
where a.ID1 = b.ID1
and b.ID1 = c.ID1
and c.ID1 = d.ID1
and (d.ID4 = 'abc1'
or d.ID4 = 'abc2'
or d.ID4 = 'abc3')
group by to_char(d.date1,'YYYY')

我想知道我每年有多少张唱片。
所以，你为什么不做你想做的？

select extract(year from d.date1) as year,
       count(*)
from table1 a join table2 b on a.id1 = b.id1
              join table3 c on c.id1 = b.id1
              join table4 d on d.id1 = c.id1
where d.id4 in ('abc1', 'abc2', 'abc3')
group by extract(year from d.date1)
order by extract(year from d.date1);

将选择列表替换为count(*)：

select count(*)
from Table1 a, Table2 b, Table3 c, Table4 d
where a.ID1 = b.ID1
and b.ID1 = c.ID1
and c.ID1 = d.ID1
and (d.ID4 = 'abc1'
or d.ID4 = 'abc2'
or d.ID4 = 'abc3')
and trunc(d.date1) between to_date('20210101','YYYYMMDD') and to_date('20211231','YYYYMMDD')

我向您推荐：

• 使用现代连接语法
• 按 predicate 使用顺序访问表以提高性能
• 消除索引列上or以避免表扫描

select count(*)
from Table4 d
join Table3 c on c.ID1 = d.ID1
join Table2 b on and b.ID1 = c.ID1
join Table1 a on a.ID1 = b.ID1
where d.ID4 in ('abc1', 'abc2', 'abc3')
and trunc(d.date1) between to_date('20210101','YYYYMMDD') and to_date('20211231','YYYYMMDD')

并在完整查询中进行相同的更改。您可能会发现它的运行速度要快得多。