oracle按区间求和,oracle 按照日期区间求和

//求两个日期之间的数据之和 //2011/03/16为起始日，2011/09/15为结束日 //没一个月为一个梯度，也就是3.16-4.15,4.16-5.15,... //数据： 日期 金额 2011/03/16 20 2011/03/17 30 2011/04/14 50 2011/04/15 50 2011/04/16 10 2011/04/17 20 2011/05/14 30 2011/05/15 40 2011/05/20 10 2011/06/17 20 2011/06/18 30 //结果： 月份 总金额 2011/04 150 2011/05 100 2011/06 10 2011/07 50 //这个问题的关键是将如何按照日期区间来统计金额 //如果我们这么想，就能够将问题简单化： //也就是进行分组查询，将日期进行对比，分别进行统计，然后再连接到一起： //解法一： select '2011/04' month,sum(val) sum_sal from t where dt between to_date('2011/03/16','yyyy/mm/dd') and to_date('2011/03/16','yyyy/mm/dd')+30 union all select '2011/05',sum(val) from t where dt between to_date('2011/04/16','yyyy/mm/dd') and to_date('2011/04/16','yyyy/mm/dd')+30 union all select '2011/06',sum(val) from t where dt between to_date('2011/05/16','yyyy/mm/dd') and to_date('2011/05/16','yyyy/mm/dd')+30 union all select '2011/07',sum(val) from t where dt between to_date('2011/06/16','yyyy/mm/dd') and to_date('2011/06/16','yyyy/mm/dd')+30) / MONTH SUM_SAL ------- ---------- 2011/04 150 2011/05 100 2011/06 10 2011/07 50 //关键问题是这么写太死板了，如果要统计连续20各月的数据呢？ //我们不是要写20个查询，然后union all呢？ //所以这个方法不可行，我们来看看更优的解法。 // //解法二 //首先，我们应该得到一个时间区段，也就是将每个时间段分别求出来， //然后再将于表进行比较，将在这个日期段之间的数据求和： with ta as( select to_date('2011/03/16','yyyy/mm/dd') sdt,to_date('2011/09/15','yyyy/mm/dd') edt from dual) select add_months(sdt,level-1) sdt,add_months(sdt,level)-1 edt from ta connect by level<=months_between(edt,sdt) SDT EDT ----------- ----------- 2011-03-16 2011-04-15 2011-04-16 2011-05-15 2011-05-16 2011-06-15 2011-06-16 2011-07-15 2011-07-16 2011-08-15 //这里还差一条数据，就是8.16-9.15这个日期段的，原因出在什么地方呢？ with ta as( select to_date('2011/03/16','yyyy/mm/dd') sdt,to_date('2011/09/15','yyyy/mm/dd') edt from dual) select months_between(edt,sdt) from ta MONTHS_BETWEEN(EDT,SDT) ----------------------- 5.96774193548387 //原来这里只是将两个日期进行了减法，并没有将其取整， //我们知道，一个date类型与一个number类型值相加，结果为date类型，并且是以天数相加的， //不足一天的将会转换为hh:mi:ss数据，但是这些我们并不关心，所以应该将这个数据向上取整： CEIL(MONTHS_BETWEEN(EDT,SDT)) ----------------------------- 6 /这样我们就得到了全部的日期区间： SDT EDT ----------- ----------- 2011-03-16 2011-04-15 2011-04-16 2011-05-15 2011-05-16 2011-06-15 2011-06-16 2011-07-15 2011-07-16 2011-08-15 2011-08-16 2011-09-15 //接下来，我们通过日期比较，然后计算求和： //也就是将test表中的日期与上面我们得到的日期区间进行比较，根据这个比较来求和： with ta as( select to_date('2011/03/16','yyyy/mm/dd') sdt,to_date('2011/09/15','yyyy/mm/dd') edt from dual) ,tb as( select add_months(sdt,level-1) sdt,add_months(sdt,level)-1 edt from ta connect by level<=ceil(months_between(edt,sdt))) select to_char(tb.edt,'yyyy/mm') month, nvl((select sum(val) from test where dt between tb.sdt and tb.edt),0) sum_sal from tb / MONTH SUM_SAL ------- ---------- 2011/04 150 2011/05 100 2011/06 10 2011/07 50 2011/08 0 2011/09 0 //也可以将没有的数据去掉 select to_char(b.edt,'yyyy/mm') month,sum(val) sum_val from test a,tb b where a.dt between b.sdt and b.edt group by to_char(b.edt,'yyyy/mm') / MONTH SUM_VAL ------- ---------- 2011/04 150 2011/05 100 2011/06 10 2011/07 50 //其实这个解法二与解法一是同样的道理，都是进行日期区间的比较； //解法二的巧妙之处在于，它将起始日期和终止日期之间的月份区间转换为一张表， //然后再将数据表test与此区间表进行比较，在日期区间的就进行求和val，最后得到结果 // //解法三： with t as( select to_date('2011/03/16','yyyy/mm/dd') dt,20 val from dual union all select to_date('2011/03/17','yyyy/mm/dd'),30 from dual union all select to_date('2011/04/14','yyyy/mm/dd'),50 from dual union all select to_date('2011/04/15','yyyy/mm/dd'),50 from dual union all select to_date('2011/04/16','yyyy/mm/dd'),10 from dual union all select to_date('2011/04/17','yyyy/mm/dd'),20 from dual union all select to_date('2011/05/14','yyyy/mm/dd'),30 from dual union all select to_date('2011/05/15','yyyy/mm/dd'),40 from dual union all select to_date('2011/05/20','yyyy/mm/dd'),10 from dual union all select to_date('2011/06/17','yyyy/mm/dd'),20 from dual union all select to_date('2011/06/18','yyyy/mm/dd'),30 from dual) select to_char(add_months(to_date('2011/4/1','yyyy/mm/dd'), trunc(months_between(dt,to_date('2011/3/16','yyyy/mm/dd')))), 'yyyy/mm') month, sum(val) sum_val from t group by trunc(months_between(dt,to_date('2011/3/16','yyyy/mm/dd'))) order by month / MONTH SUM_VAL ------- ---------- 2011/04 150 2011/05 100 2011/06 10 2011/07 50 //获取日期dt与起始日期相隔的月份，然后将其取整， //起始日期为(2011/03/16)，这样就保证了每隔一个月统计一次val //也就是没到一个月的15号为一个月的统计终止日期 SELECT MONTHS_BETWEEN(DT,TO_DATE('2011/3/16','YYYY/MM/DD') FROM T -------------- 0 0.032258064516 0.935483870967 0.967741935483 1 1.032258064516 1.935483870967 1.967741935483 2.129032258064 3.032258064516 3.064516129032 //日期之间相隔的月份一般是整数，而我们得到的是小数，所以要将小数转换为整数 //而且我们的查询从2011/04/01开始， SELECT TRUNC(MONTHS_BETWEEN(DT,TO_DATE('2011/3/16','YYYY/MM/DD'))) FROM T ----------- 0 0 0 0 1 1 1 1 2 3 3 //使用add_months函数将开始统计的月份4月加上月份相隔， //并使用to_char函数进行日期格式的转换，这样就能够得到结果中的month部分 add_months(to_date('2011/4/1','yyyy/mm/dd'), trunc(months_between(dt,to_date('2011/3/16','yyyy/mm/dd')))) //此解法的关键： //1.获取月份间隔并取整 //2.查询从04.01开始，足月增加，并截取yyyy/mm作为显示数据

原帖：http://topic.csdn.net/u/20110524/22/923863f2-56cb-4307-a2fd-c3bfb571f6df.html?71926