如何在下面的SQL代码上使SQL查询更快?

I need to make my below query faster. I used several selects and make my application very slow. Please help me to fix it.

我需要使下面的查询速度更快。我使用了几个select语句，使我的应用程序非常缓慢。请帮我修一下。

select
Branch,
(select count(*) from TCB T1 where Standard like "%FCC%" and T0.Branch=T1.Branch) as FCC,
(select count(*) from TCB T2 where (Standard like "%ISEDC%" or Standard like "%RSS%") and T0.Branch=T2.Branch) as ISEDC,
(select count(*) from TCB T3 where (Standard like "%RED%" or Standard like "%EN%") and T0.Branch=T3.Branch) as RED,
(select count(*) from TCB T4 where Standard like "%MIC%" and T0.Branch=T4.Branch) as MICJapan,
(select count(*) from TCB T5 where Standard like "%IMDA%" and T0.Branch=T5.Branch) as IMDA,
(select count(*) from TCB T6 where Standard like "%ACTA%" and T0.Branch=T6.Branch) as ACTA,
(select count(*) from TCB T7 where Standard like "%CS03%" and T0.Branch=T7.Branch) as CS03,
((select count(*) from TCB T1 where Standard like "%FCC%" and T0.Branch=T1.Branch)+
(select count(*) from TCB T2 where (Standard like "%ISEDC%" or Standard like "%RSS%") and T0.Branch=T2.Branch)+
(select count(*) from TCB T3 where (Standard like "%RED%" or Standard like "%EN%") and T0.Branch=T3.Branch)+
(select count(*) from TCB T4 where Standard like "%MIC%" and T0.Branch=T4.Branch)+
(select count(*) from TCB T5 where Standard like "%IMDA%" and T0.Branch=T5.Branch)+
(select count(*) from TCB T6 where Standard like "%ACTA%" and T0.Branch=T6.Branch)+
(select count(*) from TCB T7 where Standard like "%CS03%" and T0.Branch=T7.Branch)
) as Total

from
TCB T0
group by Branch

Result:(The result is correct but It is too slow)

结果:(结果是正确的，但太慢)

ScreenShot of result

截图的结果

1 个解决方案

#1

Use conditional aggregation:

使用条件聚合:

select Branch,
       sum(Standard like '%FCC%') as FCC,
       sum(Standard like '%ISEDC%' or Standard like '%RSS%') as ISEDC,
       . . .
from TCB 
group by Branch