日期:2014-05-17  浏览次数:20772 次

统计sql问题
一张表 A,两个字段aa(date),bb(varchar2)要统计bb这个字段中的值在每个月的数量是多少,这个sql语句怎么写?

统计结果如下,其中A,B,C为bb字段中的值

  2012-01 2012-02 2012-03 2012-04 ...
 A 10 5 0 10
 B 20 10 20 0
 C 20 5 10 20
 . ...
 .
 .
 

------解决方案--------------------
大致这样了
SQL code

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维数组,以下是一个实例,仅供参考。可能有不好的地方。
 


SQL code

  --按日统计受理后学员报表数据
  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;