速求oracle语句，今天晚上要！多谢
日期：2014-05-17 浏览次数：20889 次
速求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季度
*/
**** **** **** ****
**** **** **** ****
------解决方案--------------------
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 262
2 2 1200
3 3 5
4 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 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
------解决方案--------------------
- 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 dat
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
