oracle 按照日期区间求和

46vj7p0zz4

　　//求两个日期之间的数据之和//2011/03/16为起始日，2011/09/15为结束日//没一个月为一个梯度，也就是3.16-4.15,4.16-5.15,...//数据：日期           金额2011/03/16      20 2011/03/17      302011/04/14      502011/04/15      502011/04/16      102011/04/17      202011/05/14      302011/05/15      402011/05/20      102011/06/17      202011/06/18      30//结果：月份           总金额2011/04        1502011/05        1002011/06        102011/07        50//这个问题的关键是将如何按照日期区间来统计金额//如果我们这么想，就能够将问题简单化：//也就是进行分组查询，将日期进行对比，分别进行统计，然后再连接到一起：//解法一：select '2011/04' month,sum(val) sum_salfrom twhere dt between to_date('2011/03/16','yyyy/mm/dd')and to_date('2011/03/16','yyyy/mm/dd')+30union allselect '2011/05',sum(val)from twhere dt between to_date('2011/04/16','yyyy/mm/dd')and to_date('2011/04/16','yyyy/mm/dd')+30union allselect '2011/06',sum(val)from twhere dt between to_date('2011/05/16','yyyy/mm/dd')and to_date('2011/05/16','yyyy/mm/dd')+30union allselect '2011/07',sum(val)from twhere 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        1502011/05        1002011/06         102011/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 edtfrom taconnect by level<=months_between(edt,sdt)SDT         EDT----------- -----------2011-03-16  2011-04-152011-04-16  2011-05-152011-05-16  2011-06-152011-06-16  2011-07-152011-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 taMONTHS_BETWEEN(EDT,SDT)-----------------------5.96774193548387//原来这里只是将两个日期进行了减法，并没有将其取整，//我们知道，一个date类型与一个number类型值相加，结果为date类型，并且是以天数相加的，//不足一天的将会转换为hh:mi:ss数据，但是这些我们并不关心，所以应该将这个数据向上取整：CEIL(MONTHS_BETWEEN(EDT,SDT))-----------------------------6/这样我们就得到了全部的日期区间：SDT         EDT----------- -----------2011-03-16  2011-04-152011-04-16  2011-05-152011-05-16  2011-06-152011-06-16  2011-07-152011-07-16  2011-08-152011-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 taconnect 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_salfrom tb/MONTH      SUM_SAL------- ----------2011/04        1502011/05        1002011/06         102011/07         502011/08          02011/09          0//也可以将没有的数据去掉select to_char(b.edt,'yyyy/mm') month,sum(val) sum_valfrom test a,tb bwhere a.dt between b.sdt and b.edtgroup by to_char(b.edt,'yyyy/mm')/MONTH      SUM_VAL------- ----------2011/04        1502011/05        1002011/06         102011/07         50//其实这个解法二与解法一是同样的道理，都是进行日期区间的比较；//解法二的巧妙之处在于，它将起始日期和终止日期之间的月份区间转换为一张表，//然后再将数据表test与此区间表进行比较，在日期区间的就进行求和val，最后得到结果////解法三：with t as(select to_date('2011/03/16','yyyy/mm/dd') dt,20 val from dual union allselect to_date('2011/03/17','yyyy/mm/dd'),30 from dual union allselect to_date('2011/04/14','yyyy/mm/dd'),50 from dual union allselect to_date('2011/04/15','yyyy/mm/dd'),50 from dual union allselect to_date('2011/04/16','yyyy/mm/dd'),10 from dual union allselect to_date('2011/04/17','yyyy/mm/dd'),20 from dual union allselect to_date('2011/05/14','yyyy/mm/dd'),30 from dual union allselect to_date('2011/05/15','yyyy/mm/dd'),40 from dual union allselect to_date('2011/05/20','yyyy/mm/dd'),10 from dual union allselect to_date('2011/06/17','yyyy/mm/dd'),20 from dual union allselect 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_valfrom tgroup by trunc(months_between(dt,to_date('2011/3/16','yyyy/mm/dd')))order by month/MONTH      SUM_VAL------- ----------2011/04        1502011/05        1002011/06         102011/07         50//获取日期dt与起始日期相隔的月份，然后将其取整，//起始日期为(2011/03/16)，这样就保证了每隔一个月统计一次val//也就是没到一个月的15号为一个月的统计终止日期SELECT MONTHS_BETWEEN(DT,TO_DATE('2011/3/16','YYYY/MM/DD') FROM T--------------00.0322580645160.9354838709670.96774193548311.0322580645161.9354838709671.9677419354832.1290322580643.0322580645163.064516129032//日期之间相隔的月份一般是整数，而我们得到的是小数，所以要将小数转换为整数//而且我们的查询从2011/04/01开始，SELECT TRUNC(MONTHS_BETWEEN(DT,TO_DATE('2011/3/16','YYYY/MM/DD'))) FROM T-----------00001111233//使用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