日期:2014-05-17 浏览次数:20833 次
select bb, sum(decode(to_char(aa,'yyyy-mm'),'2012-01',1,0)) c1, --... sum(decode(to_char(aa,'yyyy-mm'),'2012-04',1,0)) c4, sum(decode(to_char(aa,'yyyy-mm'),'2012-05',1,0)) c5 --... from t1 group by bb
------解决方案--------------------
申明2维数组,以下是一个实例,仅供参考。可能有不好的地方。
--按日统计受理后学员报表数据 function fun_student_total_rep(schoolId in varchar2,startDate in date,endDate in date,rep_type in number) return clob is days int :=endDate-startDate+1; i int :=1; j int :=1; var_temp_index int; var_count int:=0; var_temp_school_name varchar2(20); var_temp_str varchar2(20); var_temp_length int:=0; var_return clob; type typeDateArray is table of varchar2(20) index by binary_integer; type dateArray is table of binary_integer index by varchar2(10); type twoArray is table of typeDateArray index by binary_integer; var_dataDate typeDateArray; var_temp_dataDate typeDateArray; var_twoArray twoArray; --日期对应列表下标 var_date dateArray; type typeCur is ref cursor; cur_data typeCur; var_sql varchar2(400); vc_id t_student_total_report.id%type; vc_nums number; vc_school_name varchar(40); vc_date date; begin var_dataDate(0):='驾校名称'; var_dataDate(days+1):='小计'; --生成标题 for tempI in 1.. days loop --生成标题02-28日(人),只保留月与日 var_dataDate(tempI):=substr(to_char(startDate+tempI-1,'yyyy-mm-dd'),6,9)||'日(人)'; var_date(to_char(startDate+tempI-1,'yyyy-mm-dd')):=tempI; var_twoArray(0):=var_dataDate; end loop; --重置默认值0 for tempI in 1.. days loop var_dataDate(tempI):='0'; end loop; var_sql:='select t.id, t.total_num as nums, t.school_name, trunc(t.report_date) as report_date from t_student_total_report t, corp_info c where report_date >=to_date('''||to_char(startDate,'yyyy-mm-dd')||''',''yyyy-mm-dd'''|| ') and report_date <=to_date('''||to_char(endDate,'yyyy-mm-dd')||''',''yyyy-mm-dd'''|| ') and t.school_id = c.school_id'; if schoolId is not null then var_sql:=var_sql||' and t.school_id= '''||schoolId||''''; dbms_output.put_line(var_sql); end if; var_sql:=var_sql||' order by c.sort_id, t.report_date'; --生成数据 open cur_data for var_sql; loop fetch cur_data into vc_id,vc_nums,vc_school_name,vc_date; exit when cur_data%Notfound; if var_temp_school_name is not null and var_temp_school_name !=vc_school_name then --一条记录 j:=j+1; var_count :=0; --重置默认值0 for tempI in 1.. days loop var_dataDate(tempI):='0'; end loop; end if; --0,驾校名称 var_temp_index:= var_date(to_char(vc_date,'yyyy-mm-dd')); var_temp_school_name:=vc_school_name; var_dataDate(var_temp_index):=vc_nums; var_dataDate(0):=var_temp_school_name; --驾校小计统计 var_count :=var_count+vc_nums; --小计 var_dataDate(days+1):=var_count; --每个驾校的记录 var_twoArray(j):=var_dataDate; end loop; if j =1 and var_temp_school_name is null then var_dataDate(0):=''; var_dataDate(days):='0'; var_dataDate(days+1):='0'; var_twoArray(j):=var_dataDate; else var_dataDate(0):=var_temp_school_name; var_dataDate(days+1):=var_count; var_twoArray(j):=var_dataDate; end if;