求助

Question

SELECT Z,
       C,
       SUM(jkl)                        jkl         , --  捐款量         
       SUM(DECODE(JZWP,'00',JZSL,0))   mianbeisl   , -- 棉被数量  
       SUM(DECODE(JZWP,'01',JZSL,0))   miantansl   , -- 棉毯数量  
       SUM(DECODE(JZWP,'02',JZSL,0))   maotansl    , -- 毛毯数量  
       SUM(DECODE(JZWP,'03',JZSL,0))   chuangdansl , -- 床单数量   
       SUM(DECODE(JZWP,'04',JZSL,0))   mianyisl    , -- 棉衣数量  
       SUM(DECODE(JZWP,'05',JZSL,0))   maoyikusl   , -- 毛衣?数量 
       SUM(DECODE(JZWP,'06',JZSL,0))   rongyisl    , -- ?衣数量    
       SUM(DECODE(JZWP,'07',JZSL,0))   piyisl      , -- 皮衣数量   
       SUM(DECODE(JZWP,'08',JZSL,0))   yurongyisl  , -- 羽?衣数量 
       SUM(DECODE(JZWP,'09',JZSL,0))   qiuyikusl   , -- 秋衣?数量 
       SUM(DECODE(JZWP,'10',JZSL,0))   chenyisl    , -- ?衣数量  
       SUM(DECODE(JZWP,'11',JZSL,0))   wenzhangsl  , -- 蚊?数量  
       SUM(DECODE(JZWP,'12',JZSL,0))   qitasl        -- 其他数量 ]
FROM mz_jk
GROUP BY Z,C

Answer 1

条件忘了WHERE jzsj BETWEEN tjkssj AND tjjzsj

Answer 2

select z,c,sum(jkl),sum(decode(jzwp,'00',jkl,0)) mianbeisl,                                 
sum(decode(jzwp,'01',jkl,0)) miantansl,sum(decode(jzwp,'02',jkl,0)) maotansl,
sum(decode(jzwp,'03',jkl,0)) chuangdansl,sum(decode(jzwp,'04',jkl,0)) mianyisl,
sum(decode(jzwp,'05',jkl,0)) maoyikusl,sum(decode(jzwp,'06',jkl,0)) rongyisl,
sum(decode(jzwp,'07',jkl,0)) piyisl,sum(decode(jzwp,'08',jkl,0)) yurongyisl,
sum(decode(jzwp,'09',jkl,0)) qiuyikusl,sum(decode(jzwp,'10',jkl,0)) mchenyisl,
sum(decode(jzwp,'11',jkl,0)) wenzhangsl,sum(decode(jzwp,'12',jkl,0)) qitasl
from mz_jk group by z,c;

Answer 3

select z,c,sum(jkl),sum(decode(jzwp,'00',jkl,0)) mianbeisl,                                 
sum(decode(jzwp,'01',jkl,0)) miantansl,sum(decode(jzwp,'02',jkl,0)) maotansl,
sum(decode(jzwp,'03',jkl,0)) chuangdansl,sum(decode(jzwp,'04',jkl,0)) mianyisl,
sum(decode(jzwp,'05',jkl,0)) maoyikusl,sum(decode(jzwp,'06',jkl,0)) rongyisl,
sum(decode(jzwp,'07',jkl,0)) piyisl,sum(decode(jzwp,'08',jkl,0)) yurongyisl,
sum(decode(jzwp,'09',jkl,0)) qiuyikusl,sum(decode(jzwp,'10',jkl,0)) mchenyisl,
sum(decode(jzwp,'11',jkl,0)) wenzhangsl,sum(decode(jzwp,'12',jkl,0)) qitasl
from mz_jk 
where jzsj>=to_date('tjkssj','yyyy-mm-dd hh24:mi:ss') and jzsj<=to_date('tjjzsj','yyyy-mm-dd hh24:mi:ss') 
group by z,c;

Answer 4

以上的方法是可以，但是如果jzwp里面的字段编码不确定的话，那就不灵活了。那样的话sql还得改。用存储过程就一次性解决这个问题了。
Create Or Replace Procedure p_test(
                                   v_tjkssj In Varchar2,v_tjjzsj In Varchar2,v_sql Out Varchar2
                                  )IsType v_cursor Is Ref Cursor;v_cur  v_cursor;
v_tmp Varchar2(4000);
v_sign Varchar2(100);
as_sql Varchar2(4000);
Begin
  as_sql :='SELECT Z, C, SUM(jkl) ';
  v_tmp := '';
  Open v_cur For Select Distinct jzwp 
                   From mz_jk 
                  Where to_char(jzsj,'yyyy-mm-dd')>=tjkssj
                   And  to_char(jzsj,'yyyy-mm-dd')<=tjjzsj;
       Loop
           Fetch v_cur into v_sign;
           exit when v_cur%NOTFOUND;
           If v_sign Is Not Null Then
             v_tmp := v_tmp||','||' SUM(DECODE(JZWP,'||v_sign||',JZSL,0)) '
           End If;
       End Loop;
           as_sql :=as_sql||v_tmp||' from mz_jk group by z,c'
  Close v_cur;
  v_sql :=as_sql;
  End p_test;