速求oracle语句，今天晚上要！谢谢

[lt1299416898]

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季度
*/
**** **** **** ****
**** **** **** ****

[ssqtjffcu]

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

[larrychen_bmw]

/*表一
季度 消费金额
*/
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

[ssqtjffcu]

[Quote=引用 3 楼 的回复:]
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
……
[/Quote]
不错！！

[大明湖畔_帅锅]

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

PS：新人注意结贴

[guo624587253]

select to_char(dt, 'Q ') ,sum(price) from sell group by to_char(dt, 'Q ') ;

[guo624587253]

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 ;

[guo624587253]

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 ;

[大明湖畔_帅锅]

这贴还没结？ 昨天发了2个 结了一个？

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

[coolkisses]

不妥，N年的 1 季度怎么可以算在一起？
逻辑有问题

[大明湖畔_帅锅]

[Quote=引用 6 楼 的回复:]

不妥，N年的 1 季度怎么可以算在一起？
逻辑有问题
[/Quote]

构造的是2012年的月份 然后提取的4个季度 如果要计算多年的每个季度 可以添加一个年份来分组

[zlb_chen]

 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