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 0PS:新人注意结贴
select jd, sum(price)
from (select id,
name,
dt,
price,
case
when dt between to_date('2012-01-01', 'yyyy-mm-dd') and
to_date('2012-04-01', 'yyyy-mm-dd') then
'1季度'
when dt between to_date('2012-04-01', 'yyyy-mm-dd') and
to_date('2012-07-01', 'yyyy-mm-dd') then
'2季度'
when dt between to_date('2012-07-01', 'yyyy-mm-dd') and
to_date('2012-10-01', 'yyyy-mm-dd') then
'3季度'
when dt between to_date('2012-10-01', 'yyyy-mm-dd') and
to_date('2013-01-01', 'yyyy-mm-dd') then
'4季度'
end jd
from sell)
group by jd
JD SUM(PRICE)
----- ----------
1季度 262
2季度 1200
3季度 5
select sum(case
when dt between to_date('2012-01-01', 'yyyy-mm-dd') and
to_date('2012-04-01', 'yyyy-mm-dd') then
price
else
0
end) "1季度",
sum(case
when dt between to_date('2012-04-01', 'yyyy-mm-dd') and
to_date('2012-07-01', 'yyyy-mm-dd') then
price
else
0
end) "2季度",
sum(case
when dt between to_date('2012-07-01', 'yyyy-mm-dd') and
to_date('2012-10-01', 'yyyy-mm-dd') then
price
else
0
end) "3季度",
sum(case
when dt between to_date('2012-10-01', 'yyyy-mm-dd') and
to_date('2013-01-01', 'yyyy-mm-dd') then
price
else
0
end) "4季度"
from sell t
1季度 2季度 3季度 4季度
---------- ---------- ---------- ----------
262 1200 5 0
/*表一
季度 消费金额
*/
with quarter as --4个季度
(select level q1 from dual connect by level <= 4),
sell_q as --某年度下,各季度汇总
(select to_char(dt, 'Q') q2, sum(price) sum_p
from sell
where dt between date '2012-01-01' and date '2012-12-31' + 0.99999
group by to_char(dt, 'Q'))
select q1 季度, nvl(sum_P, 0) 消费金额
from quarter, sell_q
where q1 = q2 (+)
order by 1;季度 消费金额
1 262
2 1200
3 5
4 0
/*表二
1季度 2季度 3季度 4季度
*/
with quarter as --4个季度
(select level q1 from dual connect by level <= 4),
sell_q as --某年度下,各季度汇总
(select to_char(dt, 'Q') q2, sum(price) sum_p
from sell
where dt between date '2012-01-01' and date '2012-12-31' + 0.99999
group by to_char(dt, 'Q')),
ret as --表1的结果
(select q1 q, nvl(sum_P, 0) p
from quarter, sell_q
where q1 = q2(+))
--将表1结果转换成横向
select (select p from ret where q = '1') 一季度,
(select p from ret where q = '2') 二季度,
(select p from ret where q = '3') 三季度,
(select p from ret where q = '4') 四季度
from dual;
一季度 二季度 三季度 四季度
262 1200 5 0
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')) 季度1 季度2 季度3 季度4
--------------------------------------
1 262 1200 5 0
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
WHEN '1' THEN price
else 0
END) "第一季度",
sum(CASE to_char(dt, 'Q')
WHEN '2' THEN price
else 0
END) "第2季度",
sum(CASE to_char(dt, 'Q')
WHEN '3' THEN price
else 0
END) "第3季度",
sum(CASE to_char(dt, 'Q')
WHEN '4' THEN price
else 0
END) "第4季度"
from sell ;
WHEN '1' THEN price
else 0
END) "第一季度",
sum(CASE to_char(dt, 'Q')
WHEN '2' THEN price
else 0
END) "第2季度",
sum(CASE to_char(dt, 'Q')
WHEN '3' THEN price
else 0
END) "第3季度",
sum(CASE to_char(dt, 'Q')
WHEN '4' THEN price
else 0
END) "第4季度"
from sell ;