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季度
*/
**** **** **** ****
**** **** **** ****
------解决方案--------------------
- SQL code
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 2622 2 12003 3 54 4 0
------解决方案--------------------
- SQL code
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 jdJD SUM(PRICE)----- ----------1季度 2622季度 12003季度 5select 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
------解决方案--------------------
- SQL code
/*表一季度 消费金额*/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_qwhere q1 = q2 (+) order by 1;季度 消费金额1 2622 12003 54 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