求助

[alike188]

我的表是这样的
create table mz_jk(/*捐赠*/
xh varchar2(10) not null, /* 序号*/
z varchar2(12) not null, /* 镇*/
c varchar2(12) not null, /* 村*/
jzrsfz varchar2(18) null, /* 身份证号*/
jzrxm varchar2(20) not null, /*捐赠人姓名*/
jzsj date not null, /*捐赠时间*/
xb char(1) not null, /* 性别*/
jkl number(12,2) null, /*捐款量*/
jzwp char(2) null, /*捐赠物品：00棉被，01棉毯、02毛毯、03床单、04棉衣、05毛衣裤、06绒衣、07皮衣、08羽绒衣、09秋衣裤、10衬衣、11蚊帐、12其他*/
jzsl number(5) null,/*捐赠数量*/
constraint pk_jkprimary key(xh)
);

统计报表
输入：
数据标示 数据项名称

镇 z
村 c
统计开始时间 tjkssj
统计截至时间 tjjzsj


输出：
数据标示 数据项名称
镇 z
村 c
捐款量 jkl
棉被数量 mianbeisl
棉毯数量 miantansl
毛毯数量 maotansl
床单数量 chuangdansl
棉衣数量 mianyisl
毛衣裤数量 maoyikusl
绒衣数量 rongyisl
皮衣数量 piyisl
羽绒衣数量 yurongyisl
秋衣裤数量 qiuyikusl
衬衣数量 chenyisl
蚊帐数量 wenzhangsl
其他数量 qitasl 请问这个存储过程如何写 !!!在此先谢了！！！

[hzwm]

其实，用存储过程完成这个功能是很简单的。

[liuyi8903]

以上的方法是可以，但是如果jzwp里面的字段编码不确定的话，那就不灵活了。那样的话sql还得改。用存储过程就一次性解决这个问题了。
Create Or Replace Procedure p_test(
v_tjkssj In Varchar2,v_tjjzsj In Varchar2,v_sql Out Varchar2
)Is

Type 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;

[alike188]

用sql语句也行，谢谢

[海清]

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

[海清]

条件忘了

WHERE jzsj BETWEEN tjkssj AND tjjzsj

[bzszp]

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;

[bzszp]

:(
也漏掉了时间条件了

[yujiabian]

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;

[alike188]

谢谢大家,我搞定后会马上给分的,多谢大家支持！！！