大家好,我在写一个片区统计的报表程序,但总觉得使用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