展开全部
create table temp(
id varchar2(10),--货号
time date,--时间
num number--销量e68a8462616964757a686964616f31333335333164
);
insert into temp values('001',to_date('2014-08-01 15:20:25','yyyy-MM-dd HH24:mi:ss'),3);
insert into temp values('002',to_date('2014-07-21 16:11:23','yyyy-MM-dd HH24:mi:ss'),2);
insert into temp values('001',to_date('2014-01-05 11:14:21','yyyy-MM-dd HH24:mi:ss'),1);
insert into temp values('002',to_date('2014-07-11 15:21:11','yyyy-MM-dd HH24:mi:ss'),2);
commit;
select t.id 货号,sum(t.moon1) 一月,sum(t.moon2) 二月,sum(t.moon7) 七月,sum(t.moon8) 八月 from (
select temp.id id,
case when to_char(temp.TIME,'yyyymm')='201401'
then SUM(temp.NUM)
else null end as MOON1,
case when to_char(temp.TIME,'yyyymm')='201402'
then sum(temp.NUM)
end moon2,
case when to_char(temp.TIME,'yyyymm')='201407'
then sum(temp.NUM)
end moon7,
case when to_char(temp.TIME,'yyyymm')='201408'
then sum(temp.NUM)
end moon8
from temp where 1=1 group BY TEMP.ID,temp.TIME) t group by id;
输出结果:
1 002 4
2 001 1 3