17,086
社区成员
发帖
与我相关
我的任务
分享
--按月同比
with t(td,tv) as(
select to_date('2011/09/03','yyyy/mm/dd'),30 from dual
union all select to_date('2012/08/03','yyyy/mm/dd'),10 from dual
union all select to_date('2012/08/02','yyyy/mm/dd'),40 from dual
union all select to_date('2012/09/04','yyyy/mm/dd'),30 from dual
union all select to_date('2012/09/05','yyyy/mm/dd'),25 from dual
)
select (sum(case when to_char(td,'yyyymm')=to_char(sysdate,'yyyymm') then tv else 0 end)
-sum(case when to_char(td,'yyyymm')=to_char(sysdate,'yyyymm')-100 then tv else 0 end))
/sum(case when to_char(td,'yyyymm')=to_char(sysdate,'yyyymm')-100 then tv else 0 end)*100
||'%'
from t;
--按月环比
with t(td,tv) as(
select to_date('2011/09/03','yyyy/mm/dd'),30 from dual
union all select to_date('2012/08/03','yyyy/mm/dd'),10 from dual
union all select to_date('2012/08/02','yyyy/mm/dd'),40 from dual
union all select to_date('2012/09/04','yyyy/mm/dd'),30 from dual
union all select to_date('2012/09/05','yyyy/mm/dd'),25 from dual
)
select (sum(case when to_char(td,'yyyymm')=to_char(sysdate,'yyyymm') then tv else 0 end)
-sum(case when to_char(td,'yyyymm')=to_char(add_months(sysdate,-1),'yyyymm') then tv else 0 end))
/sum(case when to_char(td,'yyyymm')=to_char(add_months(sysdate,-1),'yyyymm') then tv else 0 end)*100
||'%'
from t;