有一张表:INFO字段1 字段2 字段3
-----------------
A 25
B 10
B 20
C A 10
C 25
B A,C 20要求对应字段1、字段2,做字段3的合计,实际上就是得到:
A 55 即(25+10+20)
B 50 即(10+20+20)
C 55 即(10+25+20)如果只是对应字段1,统计字段3的合计,那就简单了:
select 字段1,sum(字段3) from info group by 字段1但现在要把字段2的信息也考虑进去,这样的SQL如何写?
(字段2要么为空,要么就是字段1与逗号的组合。)
-----------------
A 25
B 10
B 20
C A 10
C 25
B A,C 20要求对应字段1、字段2,做字段3的合计,实际上就是得到:
A 55 即(25+10+20)
B 50 即(10+20+20)
C 55 即(10+25+20)如果只是对应字段1,统计字段3的合计,那就简单了:
select 字段1,sum(字段3) from info group by 字段1但现在要把字段2的信息也考虑进去,这样的SQL如何写?
(字段2要么为空,要么就是字段1与逗号的组合。)
(我已经给帖子加了分)。
-----------------
A 25
B 10
B 20
C A 10
C 25
B A,C 20
is
type ResultData is ref cursor;
procedure getResultData( rst out ResultData );
end sp_Info;
/
create or replace package body sp_Info
is
procedure getResultData( rst out ResultData )
is
s varchar2(100);
i int;
f int;
cursor cur is select c2,c3 from info where c2 is not null;
begin
Select Count(*) Into i From User_Tables Where Table_Name = upper('TEMPInfo');
If i < 1 Then
execute immediate 'create global temporary table TEMPInfo(c1 varchar2(10),c3 int)On Commit PRESERVE Rows';
end if;
open cur;
loop
fetch cur into s,f;
exit when cur%notfound;
i:=instr(s,',',1);
while i>0
loop
execute immediate 'insert into tempinfo values('''||substr(s,1,i-1)||''','||f||')';
s:=substr(s,i+1,length(s)-i);
i:=instr(s,',',1);
end loop;
execute immediate 'insert into tempinfo values('''||s||''','||f||')';
end loop;
close cur;
execute immediate 'insert into tempInfo select c1,c3 from info where c2 is null';
open rst for 'select c1,sum(c3) from tempInfo group by c1';
end getResultData;
end sp_Info;