create table xsb(
XSLS NUMBER(15),
XSDATE DATE,
XSJE NUMBER(15)
)insert into xsb values (1,to_date('2009-10-7','YYYY-MM-DD HH24'),50);
insert into xsb values (2,to_date('2009-10-8','YYYY-MM-DD HH24'),50);
insert into xsb values (3,to_date('2010-09-6','YYYY-MM-DD HH24'),50);
insert into xsb values (4,to_date('2010-09-8','YYYY-MM-DD HH24'),50);
insert into xsb values (5,to_date('2010-10-07','YYYY-MM-DD HH24'),100);
insert into xsb values (6,to_date('2010-10-08','YYYY-MM-DD HH24'),100);输入开始时间 2010-10-07 截止时间 2010-10-08
得结果如下本期销售额 同比销售额 环比销售额
200 100 50
XSLS NUMBER(15),
XSDATE DATE,
XSJE NUMBER(15)
)insert into xsb values (1,to_date('2009-10-7','YYYY-MM-DD HH24'),50);
insert into xsb values (2,to_date('2009-10-8','YYYY-MM-DD HH24'),50);
insert into xsb values (3,to_date('2010-09-6','YYYY-MM-DD HH24'),50);
insert into xsb values (4,to_date('2010-09-8','YYYY-MM-DD HH24'),50);
insert into xsb values (5,to_date('2010-10-07','YYYY-MM-DD HH24'),100);
insert into xsb values (6,to_date('2010-10-08','YYYY-MM-DD HH24'),100);输入开始时间 2010-10-07 截止时间 2010-10-08
得结果如下本期销售额 同比销售额 环比销售额
200 100 50
SQL> select sum(case when xsdate between to_date('2010-10-7','yyyy-mm-dd') and to_date('2010-10-8','yyyy-mm-dd') then xsje else 0 end) "本期销售额",
2 sum(case when xsdate between add_months(to_date('2010-10-7','yyyy-mm-dd'),-12) and add_months(to_date('2010-10-8','yyyy-mm-dd'),-12) then xsje else 0 end) "同比销售额",
3 sum(case when xsdate between add_months(to_date('2010-10-7','yyyy-mm-dd'),-1) and add_months(to_date('2010-10-8','yyyy-mm-dd'),-1) then xsje else 0 end) "环比销售额"
4 from xsb
5 where xsdate between to_date('2010-10-7','yyyy-mm-dd') and to_date('2010-10-8','yyyy-mm-dd')
6 or xsdate between add_months(to_date('2010-10-7','yyyy-mm-dd'),-1) and add_months(to_date('2010-10-8','yyyy-mm-dd'),-1)
7 or xsdate between add_months(to_date('2010-10-7','yyyy-mm-dd'),-12) and add_months(to_date('2010-10-8','yyyy-mm-dd'),-12);
本期销售额 同比销售额 环比销售额
---------- ---------- ----------
200 100 50
SQL>