一张表 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
. ...
.
.
A 10 5 0 10
B 20 10 20 0
C 20 5 10 20
. ...
.
.
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
这样应该是没问题的，如果你想统计某一个时间段的，你就把
“sum(decode(to_char(aa,'yyyy-mm'),'2012-01',1,0)) c1” 循环一下，然后组成sql，去统计就oK了。
不知道我写的能实现不，我也没试，随手用记事本敲了一下。
如果不对，麻烦跟我说一下。select to_char(a.aa,'yyyy-mm') tmpdate, sum(a.bb) from A a
where a.tmpdate in
(select distinct to_char(b.aa,'yyyy-mm') from A b)
group by a.tmpdate;
我也是新手
--bb字段里面存的是第一列的a,b,c。以上用sum(bb)的都错了。。
--测试表创建
create table a
(
aa date,
bb varchar2(20)
)
--测试数据
insert into a(
select sysdate-30,'a' from dual union all
select sysdate-60,'b' from dual union all
select sysdate-30,'c' from dual union all
select sysdate-30,'a' from dual union all
select sysdate-90,'b' from dual union all
select sysdate+30,'c' from dual union all
select sysdate+60,'a' from dual union all
select sysdate+90,'b' from dual union all
select sysdate+120,'c' from dual union all
select sysdate-120,'a' from dual union all
select sysdate+180,'b' from dual union all
select sysdate+150,'c' from dual union all
select sysdate-60,'a' from dual union all
select sysdate+90,'b' from dual union all
select sysdate+30,'c' from dual union all
select sysdate+30,'a' from dual union all
select sysdate+30,'b' from dual union all
select sysdate+60,'c' from dual union all
select sysdate-60,'a' from dual union all
select sysdate-60,'b' from dual
)
--查询
select
a1.bb as 类别,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%1月%') as 一月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%2月%') as 二月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%3月%') as 三月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%4月%') as 四月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%5月%') as 五月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%6月%') as 六月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%7月%') as 七月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%8月%') as 八月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%9月%') as 九月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%10月%') as 十月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%11月%') as 十一月,
(select count(a2.bb) from a a2 where a2.bb=a1.bb and a2.aa like '%12月%') as 十二月
from a a1
group by a1.bb
--查询结果
a 1 0 2 2 0 1 1 0 0 0 0 0
b 1 1 2 0 0 1 0 2 0 0 1 0
c 0 0 0 1 0 2 1 0 1 1 0 0
--按日统计受理后学员报表数据
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;
--驾校的受理人员数量 -- dbms_output.put_line('var_twoArray.count = '||var_twoArray.count); --计算日合计
for x in 1..var_twoArray.count loop
if not var_twoArray.exists(x) then
exit;
end if;
var_dataDate :=var_twoArray(x);
-- dbms_output.put_line('x = '||x);
for m in 1.. var_dataDate.count loop
if not var_dataDate.exists(m) then
exit;
end if;
-- dbms_output.put('m = '||m);
dbms_output.put('value = '||var_dataDate(m));
if not var_temp_dataDate.exists(m) then
var_temp_dataDate(m):=var_dataDate(m);
else
var_temp_dataDate(m):=var_temp_dataDate(m)+var_dataDate(m);
end if;
end loop;
dbms_output.put_line(' ');
end loop;
var_temp_dataDate(0):='合计';
var_twoArray(var_twoArray.count):=var_temp_dataDate; --打印,拼装
for x in 0..var_twoArray.count loop
if not var_twoArray.exists(x) then
exit;
end if;
var_dataDate :=var_twoArray(x);
-- dbms_output.put_line('x = '||x);
for m in 0.. var_dataDate.count loop
if not var_dataDate.exists(m) then
exit;
end if;
-- dbms_output.put('m = '||m);
-- dbms_output.put('value = '||var_dataDate(m));
var_temp_str:='^^';
var_return :=var_return||var_dataDate(m)||'^^';
end loop;
-- dbms_output.put_line('var_return'||var_return);
var_return :=var_return||'$$';
end loop;
return var_return;
end fun_student_total_rep;