一条相关子查询的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_upfrom gc_t gcleft join ge_t geon gc.agent_id  = ge.agent_id and gc.freq = ge.freq and gc.programid = ge.programidleft join ga_t aon 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_upfrom    gc_t gcleft join ge_t geon  gc.agent_id  = ge.agent_id   and gc.freq = ge.freqand gc.programid = ge.programidleft join ga_t aon 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_upfrom (   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_upfrom    gc_t gcleft join ge_t geon  gc.agent_id  = ge.agent_id   and gc.freq = ge.freqand gc.programid = ge.programidleft join ga_t aon a.agentid = gc.agent_id) tgroup by program,agent_id,freq,programid,agentName,data_up