oracle case when sum,关于SUM((CASE When的sql语句优化
大家好，我在写一个片区统计的报表程序，但总觉得使用sum((case when...这种语句在性能上不是太好，请大家出一下主意。
语句如下:
select
SUM((CASE When pq in ('p01') and test1 in('001') Then 1 Else 0 End)) into p01 ,
SUM((CASE When pq in ('p01') and test1 in('002') Then 1 Else 0 End)) into p02 ,
SUM((CASE When pq in ('p01') and test1 in('003') Then 1 Else 0 End)) into p03 ,
SUM((CASE When pq in ('p01') and test1 in('004') Then 1 Else 0 End)) into p04 ,
SUM((CASE When pq in ('p01') and test1 in('005') Then 1 Else 0 End)) into p05 ,
SUM((CASE When pq in ('p01') and test1 in('006') Then 1 Else 0 End)) into p06 ,
SUM((CASE When pq in ('p01') and test1 in('007') Then 1 Else 0 End)) into p07 ,
SUM((CASE When pq in ('p01') and test1 in('008') Then 1 Else 0 End)) into p08 ,
SUM((CASE When pq in ('p01') and test1 in('009') Then 1 Else 0 End)) into p09 ,
SUM((CASE When pq in ('p01') and test1 in('010') Then 1 Else 0 End)) into p10 ,
SUM((CASE When pq in ('p01') and test1 in('012') Then 1 Else 0 End)) into p11 ,
SUM((CASE When pq in ('p01') and test1 in('013') Then 1 Else 0 End)) into p12 ,
SUM((CASE When pq in ('p01') and test1 in('014') Then 1 Else 0 End)) into p13 ,
SUM((CASE When pq in ('p01') and test1 in('015') Then 1 Else 0 End)) into p14 ,
SUM((CASE When pq in ('p01') and test1 in('016') Then 1 Else 0 End)) into p15 ,
,
SUM((CASE When pq in ('p02') and test1 in('001') Then 1 Else 0 End)) into pz01 ,
SUM((CASE When pq in ('p02') and test1 in('002') Then 1 Else 0 End)) into pz02 ,
SUM((CASE When pq in ('p02') and test1 in('003') Then 1 Else 0 End)) into pz03 ,
SUM((CASE When pq in ('p02') and test1 in('004') Then 1 Else 0 End)) into pz04 ,
SUM((CASE When pq in ('p02') and test1 in('005') Then 1 Else 0 End)) into pz05 ,
SUM((CASE When pq in ('p02') and test1 in('006') Then 1 Else 0 End)) into pz06 ,
SUM((CASE When pq in ('p02') and test1 in('007') Then 1 Else 0 End)) into pz07 ,
SUM((CASE When pq in ('p02') and test1 in('008') Then 1 Else 0 End)) into pz08 ,
SUM((CASE When pq in ('p02') and test1 in('009') Then 1 Else 0 End)) into pz09 ,
SUM((CASE When pq in ('p02') and test1 in('010') Then 1 Else 0 End)) into pz10 ,
SUM((CASE When pq in ('p02') and test1 in('012') Then 1 Else 0 End)) into pz11 ,
SUM((CASE When pq in ('p02') and test1 in('013') Then 1 Else 0 End)) into pz12 ,
SUM((CASE When pq in ('p02') and test1 in('014') Then 1 Else 0 End)) into pz13 ,
SUM((CASE When pq in ('p02') and test1 in('015') Then 1 Else 0 End)) into pz14 ,
SUM((CASE When pq in ('p02') and test1 in('016') Then 1 Else 0 End)) into pz15 ,
,
,
SUM((CASE When pq in ('p03') and test1 in('001') Then 1 Else 0 End)) into pz01a ,
SUM((CASE When pq in ('p03') and test1 in('002') Then 1 Else 0 End)) into pz02a ,
SUM((CASE When pq in ('p03') and test1 in('003') Then 1 Else 0 End)) into pz03a ,
SUM((CASE When pq in ('p03') and test1 in('004') Then 1 Else 0 End)) into pz04a ,
SUM((CASE When pq in ('p03') and test1 in('005') Then 1 Else 0 End)) into pz05a ,
SUM((CASE When pq in ('p03') and test1 in('006') Then 1 Else 0 End)) into pz06a ,
SUM((CASE When pq in ('p03') and test1 in('007') Then 1 Else 0 End)) into pz07a ,
SUM((CASE When pq in ('p03') and test1 in('008') Then 1 Else 0 End)) into pz08a ,
SUM((CASE When pq in ('p03') and test1 in('009') Then 1 Else 0 End)) into pz09a ,
SUM((CASE When pq in ('p03') and test1 in('010') Then 1 Else 0 End)) into pz10a ,
SUM((CASE When pq in ('p03') and test1 in('012') Then 1 Else 0 End)) into pz11a ,
SUM((CASE When pq in ('p03') and test1 in('013') Then 1 Else 0 End)) into pz12a ,
SUM((CASE When pq in ('p03') and test1 in('014') Then 1 Else 0 End)) into pz13a ,
SUM((CASE When pq in ('p03') and test1 in('015') Then 1 Else 0 End)) into pz14a ,
SUM((CASE When pq in ('p03') and test1 in('016') Then 1 Else 0 End)) into pz15a ,
,
,
SUM((CASE When pq in ('p04') and test1 in('001') Then 1 Else 0 End)) into pz01ab ,
SUM((CASE When pq in ('p04') and test1 in('002') Then 1 Else 0 End)) into pz02ab ,
SUM((CASE When pq in ('p04') and test1 in('003') Then 1 Else 0 End)) into pz03ab ,
SUM((CASE When pq in ('p04') and test1 in('004') Then 1 Else 0 End)) into pz04ab ,
SUM((CASE When pq in ('p04') and test1 in('005') Then 1 Else 0 End)) into pz05ab ,
SUM((CASE When pq in ('p04') and test1 in('006') Then 1 Else 0 End)) into pz06ab ,
SUM((CASE When pq in ('p04') and test1 in('007') Then 1 Else 0 End)) into pz07ab ,
SUM((CASE When pq in ('p04') and test1 in('008') Then 1 Else 0 End)) into pz08ab ,
SUM((CASE When pq in ('p04') and test1 in('009') Then 1 Else 0 End)) into pz09ab ,
SUM((CASE When pq in ('p04') and test1 in('010') Then 1 Else 0 End)) into pz10ab ,
SUM((CASE When pq in ('p04') and test1 in('012') Then 1 Else 0 End)) into pz11ab ,
SUM((CASE When pq in ('p04') and test1 in('013') Then 1 Else 0 End)) into pz12ab ,
SUM((CASE When pq in ('p04') and test1 in('014') Then 1 Else 0 End)) into pz13ab ,
SUM((CASE When pq in ('p04') and test1 in('015') Then 1 Else 0 End)) into pz14ab ,
SUM((CASE When pq in ('p04') and test1 in('016') Then 1 Else 0 End)) into pz15ab ,
...
from test
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
0
0- 0
已为社区贡献4条内容
所有评论(0)