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

       

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

    结果如下：

 

   

      欢迎提出更好的写法。

      全文完。