当前位置: 代码迷 >> Oracle管理 >> 速求oracle语句,今天晚上要!多谢
  详细解决方案

速求oracle语句,今天晚上要!多谢

热度:32   发布时间:2016-04-24 05:21:50.0
速求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
  相关解决方案