create table sell(
id int not null primary key ,
name varchar(100) not null,
dt date not null,
price decimal (10,2) not null
);
select to_char(dt,'mm') from sell
insert into sell(id, name, dt, price)
select 1, '香烟' ,to_date('2012-01-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),12.00 from dual
union
select 2, '衣服' ,to_date('2012-03-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),240.00 from dual
union
select 3, '手机' ,to_date('2012-04-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),1200.00 from dual
union
select 4, '可乐' ,to_date('2012-08-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),5.00 from dual
union
select 5, '啤酒' ,to_date('2012-12-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),10.00 from dual
--1
select jd,sum(price) as je
from (select case when to_char(dt,'mm') between '01' and '03' then 1
when to_char(dt,'mm') between '04' and '06' then 2
when to_char(dt,'mm') between '07' and '09' then 3
when to_char(dt,'mm') between '10' and '12' then 4 end as jd,
price from sell
) t
group by jd
order by jd;
--2
select sum(aa) 第1季度,sum(bb) 第2季度 ,sum(cc) 第3季度,sum(dd) 第4季度 from (
select case jd when 1 then je end as aa,case jd when 2 then je end as bb,
case jd when 3 then je end as cc,case jd when 4 then je end as dd from (
select jd,sum(price) as je
from (select case when to_char(dt,'mm') between '01' and '03' then 1
when to_char(dt,'mm') between '04' and '06' then 2
when to_char(dt,'mm') between '07' and '09' then 3
when to_char(dt,'mm') between '10' and '12' then 4 end as jd,
price from sell
) t
group by jd ) a )b
