select syz '',a.lx01 lx01,b.lx02 lx02,c.lx03 lx03 from t,
(select syz,count(lx) lx01 from t where lx='lx01'
group by syz) a,
(select syz,count(lx) lx02 from t where lx='lx02'
group by syz) b,
(select syz,count(lx) lx03 from t where lx='lx03'
group by syz) c
group by syz
(select syz,count(lx) lx01 from t where lx='lx01'
group by syz) a,
(select syz,count(lx) lx02 from t where lx='lx02'
group by syz) b,
(select syz,count(lx) lx03 from t where lx='lx03'
group by syz) c
group by syz
(select count(*) from tablename b where a.syz=b.syz and lx='lx01') lx01,
(select count(*) from tablename c where a.syz=c.syz and lx='lx02') lx02,
(select count(*) from tablename d where a.syz=d.syz and lx='lx03') lx03
from tablename a
select distinct syz,
(select count(*) from tablename b where a.syz=b.syz and lx='lx01') lx01,
(select count(*) from tablename c where a.syz=c.syz and lx='lx02') lx02,
(select count(*) from tablename d where a.syz=d.syz and lx='lx03') lx03
from tablename a
select syz,sum(decode(lx,'lx01',1,0)) lx01,sum(decode(lx,'lx02',1,0)) lx02,sum(decode(lx,'lx03',1,0)) lx03 from table_name group by syz;
那该如何实现呢?我想将其转换为一个存储过程,请问应该如何实现我对存储过程一窍不通
as
cursor t_sor1 is
select lx from tabname group by lx;
cursor t_sor2(p_lx varchar2) is
select syz,sum(decode(lx,p_lx,1,0) sum_num from tabname group by syz;
num1 number;
num2 number;
num3 number;
begin
for v_sor1 in t_sor1 loop
for v_sor2 in t_sor2(v_sor1.lx) loop
if v_sor2.syz='syz01' then
num1:=num1+v_sor2.sum_num;
elsif v_sor2.syz='syz01' then
num2:=num2+v_sor2.sum_num;
else
num3:=num3+v_sor2.sum_num;
end if;
end loop;
end loop;
dbms_output.put_line('syz01',num1);
....
end;
/