create or replace procedure proc4(qihao in varchar2)
is
sr_id number;
cp_id varchar2(50);
kh_id varchar2(50);
gs_id varchar2(50);type cur is ref cursor ;
cur_cpkh cur ;begin
open cur_cpkh for select sum(sr) ,c.col_1_1_214_14 ,
case s.www06
when 12 then 'KH09010101'
when 13 then'KH09010102'
when 14 then 'KH09010103'
when 16 then 'KH09010201'
when 17 then 'KH09010202'
when 18 then 'KH09010203'
when 20 then 'KH09010301'
when 21 then 'KH09010302'
when 22 then 'KH09010303'
when 23 then 'KH09010304'
when 25 then 'KH09010401'
when 26 then 'KH09010402'
when 27 then 'KH09010403'
when 29 then 'KH09010501'
when 30 then 'KH09010502'
when 31 then 'KH09010503'
when 32 then 'KH09010504'
when 33 then 'KH09010505'
when 34 then 'KH09010506'
when 60 then 'KH0905'
when 99 then 'KH0903'
else '其他' end ,
s.gsdm from sab4 s, cpdtb c where s.qijian=qijian and c.col_1_1_214_3=s.www02 and c.col_1_1_214_14 is not null group by s.gsdm, case s.www06
when 12 then 'KH09010101'
when 13 then'KH09010102'
when 14 then 'KH09010103'
when 16 then 'KH09010201'
when 17 then 'KH09010202'
when 18 then 'KH09010203'
when 20 then 'KH09010301'
when 21 then 'KH09010302'
when 22 then 'KH09010303'
when 23 then 'KH09010304'
when 25 then 'KH09010401'
when 26 then 'KH09010402'
when 27 then 'KH09010403'
when 29 then 'KH09010501'
when 30 then 'KH09010502'
when 31 then 'KH09010503'
when 32 then 'KH09010504'
when 33 then 'KH09010505'
when 34 then 'KH09010506'
when 60 then 'KH0905'
when 99 then 'KH0903'
else '其他' end ,c.col_1_1_214_14 ;
loop
fetch cur_cpkh into sr_id,cp_id,kh_id,gs_id;
exit when cur_cpkh%notfound;
end loop ;
end proc4;上面的是使用case进行替换的 将(12,13,14)替换成(kh4974456)这一类的数据select sum(sr) ,c.col_1_1_214_14 ,s.www06 ,s.gsdm from sab4 s, cpdtb c where s.qijian=201105 and c.col_1_1_214_3=s.www02 and c.col_1_1_214_14 is not null group by s.gsdm,s.www06,c.col_1_1_214_14;
第一个数据用select count(*) from 上上一张表总数是9364,下面的一张表是9041条数据求解
select sum(sr) ,c.col_1_1_214_14 ,
case s.www06
when 12 then 'KH09010101'
when 13 then'KH09010102'
when 14 then 'KH09010103'
when 16 then 'KH09010201'
when 17 then 'KH09010202'
when 18 then 'KH09010203'
when 20 then 'KH09010301'
when 21 then 'KH09010302'
when 22 then 'KH09010303'
when 23 then 'KH09010304'
when 25 then 'KH09010401'
when 26 then 'KH09010402'
when 27 then 'KH09010403'
when 29 then 'KH09010501'
when 30 then 'KH09010502'
when 31 then 'KH09010503'
when 32 then 'KH09010504'
when 33 then 'KH09010505'
when 34 then 'KH09010506'
when 60 then 'KH0905'
when 99 then 'KH0903'
else '其他' end ,
s.gsdm from sab4 s, cpdtb c where s.qijian=qijian and c.col_1_1_214_3=s.www02 and c.col_1_1_214_14 is not null group by s.gsdm, case s.www06
when 12 then 'KH09010101'
when 13 then'KH09010102'
when 14 then 'KH09010103'
when 16 then 'KH09010201'
when 17 then 'KH09010202'
when 18 then 'KH09010203'
when 20 then 'KH09010301'
when 21 then 'KH09010302'
when 22 then 'KH09010303'
when 23 then 'KH09010304'
when 25 then 'KH09010401'
when 26 then 'KH09010402'
when 27 then 'KH09010403'
when 29 then 'KH09010501'
when 30 then 'KH09010502'
when 31 then 'KH09010503'
when 32 then 'KH09010504'
when 33 then 'KH09010505'
when 34 then 'KH09010506'
when 60 then 'KH0905'
when 99 then 'KH0903'
else '其他' end ,c.col_1_1_214_14 ;
-- 是你表连接的问题!-- 你先整清楚各表之间 的业务关系是什么?
-- 然后:整清楚各表之间的连接条件有哪些?是不是哪两个表连接的时候,您给的连接条件不够的原因!
case s.www06
when 12 then 'KH09010101'
when 13 then'KH09010102'
when 14 then 'KH09010103'
when 16 then 'KH09010201'
when 17 then 'KH09010202'
when 18 then 'KH09010203'
when 20 then 'KH09010301'
when 21 then 'KH09010302'
when 22 then 'KH09010303'
when 23 then 'KH09010304'
when 25 then 'KH09010401'
when 26 then 'KH09010402'
when 27 then 'KH09010403'
when 29 then 'KH09010501'
when 30 then 'KH09010502'
when 31 then 'KH09010503'
when 32 then 'KH09010504'
when 33 then 'KH09010505'
when 34 then 'KH09010506'
when 60 then 'KH0905'
when 99 then 'KH0903'
else '其他' end ,s.gsdm
from sab4 s, cpdtb c where s.qijian=qijian
and c.col_1_1_214_3=s.www02
and c.col_1_1_214_14 is not null
group by s.gsdm,
case s.www06
when 12 then 'KH09010101'
when 13 then'KH09010102'
when 14 then 'KH09010103'
when 16 then 'KH09010201'
when 17 then 'KH09010202'
when 18 then 'KH09010203'
when 20 then 'KH09010301'
when 21 then 'KH09010302'
when 22 then 'KH09010303'
when 23 then 'KH09010304'
when 25 then 'KH09010401'
when 26 then 'KH09010402'
when 27 then 'KH09010403'
when 29 then 'KH09010501'
when 30 then 'KH09010502'
when 31 then 'KH09010503'
when 32 then 'KH09010504'
when 33 then 'KH09010505'
when 34 then 'KH09010506'
when 60 then 'KH0905'
when 99 then 'KH0903'
else '其他' end ,c.col_1_1_214_14 ;
sum(sr) ,c.col_1_1_214_14 ,s.www06 , s.gsdm
from sab4 s, cpdtb c where s.qijian=qijian
and c.col_1_1_214_3=s.www02
and c.col_1_1_214_14 is not null
group by s.gsdm,
s.www06,
c.col_1_1_214_14;