小弟跪求 如何查询同比与环比

[fanzs007]

数据库 oracle

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

如何写查询代码
string ls_value1,ls_value2,ls_filter,sql
ls_value1=f_getdata(uo_1)
ls_value2=f_getdata(uo_2)

dw_1.settransobject(sqlca);

ls_filter = "" //?????
sql="" //?????
dw_1.setsqlselect(sql)
dw_1.retrieve();
dw_1.setrowfocusindicator(Hand!)

[glint]

建议搞成存储过程在PB中调用

[fanzs007]

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>

[fanzs007]

如何写查询代码 ????