[简单]oracle当日当月当年销量统计

zmxdmdwlpz

　　

with tmp_t as(
select 1 as id,to_date('2014-06-01','yyyy-mm-dd') as v_date,1 as v_num from dual union all
select 1,to_date('2014-06-02','yyyy-mm-dd') ,2 a from dual union all
select 1,to_date('2014-06-12','yyyy-mm-dd') ,3 a from dual union all
select 1,to_date('2014-06-22','yyyy-mm-dd') ,4 a from dual union all
select 1,to_date('2014-05-22','yyyy-mm-dd') ,3 a from dual union all
select 1,to_date('2014-03-22','yyyy-mm-dd') ,7 a from dual union all
select 2,to_date('2014-06-02','yyyy-mm-dd') ,2 a from dual union all
select 2,to_date('2014-06-07','yyyy-mm-dd') ,5 a from dual union all
select 2,to_date('2014-06-17','yyyy-mm-dd') ,3 a from dual union all
select 2,to_date('2014-07-17','yyyy-mm-dd') ,3 a from dual union all
select 2,to_date('2014-08-17','yyyy-mm-dd') ,8 a from dual
)  
select aa.id, sum(aa.current_num), sum(aa.month_num), sum(aa.year_num)
from (select id,
sum(case
when trunc(v_date) =
trunc(to_date('2014-06-02', 'yyyy-mm-dd')) then
v_num
else
0
end) current_num,
sum(v_num) month_num,
max(0) year_num
from tmp_t
where 1 = 1
and v_date < last_day(to_date('2014-06-02', 'yyyy-mm-dd')) + 1
and v_date >=
trunc(add_months(last_day(to_date('2014-06-02', 'yyyy-mm-dd')),
-1) + 1)
group by id
union all
select id, max(0) current_num, max(0) month_num, sum(v_num) year_num
from tmp_t
where 1 = 1
and v_date< add_months(trunc(to_date('2014-06-02', 'yyyy-mm-dd'),'yyyy'),12)
and v_date >= trunc(to_date('2014-06-02', 'yyyy-mm-dd'), 'yyyy')
group by id) aa
group by aa.id
　　 结果如下：
　　
　　

      欢迎提出更好的写法。
　　      全文完。