一条相关子查询的SQL优化

原sql如下：

select distinct gc.program,
  gc.agent_id,
  gc.freq,
  gc.programid,
  a.agentName,
  (select count(id) from ge_t  where alert_type='add' and agent_id = gc.agent_id and freq = gc.freq  and  programid = gc.programid
  ) as addNum,
  (select count(id) from ge_t  where alert_type='del' and agent_id = gc.agent_id and freq = gc.freq  and  programid = gc.programid
  ) as reduceNum,
  (select count(id) from ge_t  where alert_type='change' and agent_id = gc.agent_id and freq = gc.freq  and  programid = gc.programid
  ) as changeNum,
  to_char(ge.date_up,'%Y-%m-%d') as data_up
from gc_t gc
left join ge_t ge
on gc.agent_id  = ge.agent_id and gc.freq = ge.freq and gc.programid = ge.programid
left join ga_t a
on a.agentid = gc.agent_id

该sql的问题是在select子查询中的count汇总，和from中的表进行关联，如下：

(select count(id) from ge_t  where alert_type='add' and agent_id = gc.agent_id and freq = gc.freq  and  programid = gc.programid
  ) as addNum

这种写法数据库会对表的每一行做1次关联汇总，非常的耗时，建议改写如下：

首先，根据条件将子查询的部分改写成case when语句，置为1或0，如下：

select  gc.program,
  gc.agent_id,
  gc.freq,
  gc.programid,
  a.agentName,
  case when alert_type='add' then 1 else 0 end as addNum,
  case when alert_type='del' then 1 else 0 end as reduceNum,
  case when alert_type='change' then 1 else 0 end as changeNum,        
  to_char(ge.date_up,'%Y-%m-%d')as data_up
from    gc_t gc
left join ge_t ge
on  gc.agent_id  = ge.agent_id   
and gc.freq = ge.freq
and gc.programid = ge.programid
left join ga_t a
on a.agentid = gc.agent_id

然后，对case when的列进行sum汇总，最终改写的sql如下：

select program,agent_id,freq,programid,agentName,
  sum(addNum) as addNum, sum(reduceNum) as reduceNum,sum(changeNum) as changeNum,
  data_up
from (   
select  gc.program,
  gc.agent_id,
  gc.freq,
  gc.programid,
  a.agentName,
  case when alert_type='add' then 1 else 0 end as addNum,
  case when alert_type='del' then 1 else 0 end as reduceNum,
  case when alert_type='change' then 1 else 0 end as changeNum,        
  to_char(ge.date_up,'%Y-%m-%d')as data_up
from    gc_t gc
left join ge_t ge
on  gc.agent_id  = ge.agent_id   
and gc.freq = ge.freq
and gc.programid = ge.programid
left join ga_t a
on a.agentid = gc.agent_id
) t
group by program,agent_id,freq,programid,agentName,data_up