create table sell( id int not null primary key ,
name varchar(100) not null,
dt date not null,
price decimal (10,2) not null
);
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-01-01 12:03:00','yyyy-MM-dd HH24:mi:ss'),10.00 from dual
/*表一
季度 消费金额
*/
**** ****
**** ****/*表二
1季度 2季度 3季度 4季度
*/
**** **** **** ****
**** **** **** ****
name varchar(100) not null,
dt date not null,
price decimal (10,2) not null
);
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-01-01 12:03:00','yyyy-MM-dd HH24:mi:ss'),10.00 from dual
/*表一
季度 消费金额
*/
**** ****
**** ****/*表二
1季度 2季度 3季度 4季度
*/
**** **** **** ****
**** **** **** ****
select to_char(c1,'q') 季度,nvl(sum(price),0) 消费金额 from
(select add_months(date'2011-12-01',level) c1
from dual
connect by level <= 12) a left join sell b on to_char(c1,'yyyy-mm')=to_char(dt,'yyyy-mm')
group by to_char(c1,'q')
order by to_char(c1,'q')
季度 消费金额
----------------------------
1 1 262
2 2 1200
3 3 5
4 4 0
262 1200 5 0
就用这种形式,我刚学orcal,还不大懂!
select sum(nvl(q1,0)) 季度1,sum(nvl(q2,0)) 季度2,sum(nvl(q3,0)) 季度3,sum(nvl(q4,0)) 季度4
from
(select case when to_char(c1,'q')=1 then sum(price) end q1,
case when to_char(c1,'q')=2 then sum(price) end q2,
case when to_char(c1,'q')=3 then sum(price) end q3,
case when to_char(c1,'q')=4 then sum(price) end q4
from
(select add_months(date'2011-12-01',level) c1
from dual
connect by level <= 12) a,sell b
where to_char(c1,'yyyy-mm')=to_char(dt,'yyyy-mm')
group by to_char(c1,'q')
order by to_char(c1,'q')) 季度1 季度2 季度3 季度4
--------------------------------------
1 262 1200 5 0
(
id int not null primary KEY,
name varchar(100) not null,
dt date not null,
price decimal (10,2) not null
);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-01-01 12:03:00','yyyy-MM-dd HH24:mi:ss'),10.00 from dualSELECT * FROM sell;
测试结果: