速求oracle语句，今天晚上要！谢谢create table sell(id int not null primary key ,name varchar(100) not
速求oracle语句，今天晚上要！谢谢
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
[解决办法]
这贴还没结？ 昨天发了2个 结了一个？
- SQL code
select sum(nvl(q1,0)) 季度1,sum(nvl(q2,0)) 季度2,sum(nvl(q3,0)) 季度3,sum(nvl(q4,0)) 季度4from (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
[解决办法]
不妥，N年的 1 季度怎么可以算在一起？
逻辑有问题
[解决办法]
[解决办法]
- SQL code
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 dualunion select 2, '衣服' ,to_date('2012-03-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),240.00 from dualunion select 3, '手机' ,to_date('2012-04-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),1200.00 from dualunion select 4, '可乐' ,to_date('2012-08-01 12:03:00','yyyy-mm-dd hh24:mi:ss'),5.00 from dualunion 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 ) tgroup by jdorder by jd;--2select 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 ) tgroup by jd ) a )b
[解决办法]
select to_char(dt, 'Q ') ,sum(price) from sell group by to_char(dt, 'Q ') ;
[解决办法]
SELECT sum(CASE to_char(dt, 'Q ')
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 ;
------解决方案--------------------
SELECT sum(CASE to_char(dt, 'Q')
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 ;