我们这个项目有一个设定起始日为这个月的第一天
比如2011/03/16为 起始日,2011/09/15为结束日查出 2011/03/16 到 2011/04/15 为4月份的数据
2011/04/16 到 2011/05/15 为5月份的数据
2011/05/16 到 2011/06/15 为6月份的数据
2011/06/16 到 2011/07/15 为7月份的数据
2011/07/16 到 2011/08/15 为8月份的数据
2011/08/16 到 2011/09/15 为9月份的数据
比如 :
日期 金额
2011/03/16 20
2011/03/17 30
2011/04/14 50
2011/04/15 50
以上为4月份的数据
2011/04/16 10
2011/04/17 20
2011/05/14 30
2011/05/15 40
以上为5月份的数据
2011/05/20 10
以上为6月份的数据
2011/06/17 20
2011/06/18 30
以上为7月份的数据
查出的结果应该为
月份 总金额
2011/04 150
2011/05 100
2011/06 10
2011/07 50
比如2011/03/16为 起始日,2011/09/15为结束日查出 2011/03/16 到 2011/04/15 为4月份的数据
2011/04/16 到 2011/05/15 为5月份的数据
2011/05/16 到 2011/06/15 为6月份的数据
2011/06/16 到 2011/07/15 为7月份的数据
2011/07/16 到 2011/08/15 为8月份的数据
2011/08/16 到 2011/09/15 为9月份的数据
比如 :
日期 金额
2011/03/16 20
2011/03/17 30
2011/04/14 50
2011/04/15 50
以上为4月份的数据
2011/04/16 10
2011/04/17 20
2011/05/14 30
2011/05/15 40
以上为5月份的数据
2011/05/20 10
以上为6月份的数据
2011/06/17 20
2011/06/18 30
以上为7月份的数据
查出的结果应该为
月份 总金额
2011/04 150
2011/05 100
2011/06 10
2011/07 50
--目前没有想到什么好的方法:
--下面写得比较笨:
SQL> with t as(
2 select to_date('2011/03/16','yyyy/mm/dd') dt,20 val from dual union all
3 select to_date('2011/03/17','yyyy/mm/dd'),30 from dual union all
4 select to_date('2011/04/14','yyyy/mm/dd'),50 from dual union all
5 select to_date('2011/04/15','yyyy/mm/dd'),50 from dual union all
6 select to_date('2011/04/16','yyyy/mm/dd'),10 from dual union all
7 select to_date('2011/04/17','yyyy/mm/dd'),20 from dual union all
8 select to_date('2011/05/14','yyyy/mm/dd'),30 from dual union all
9 select to_date('2011/05/15','yyyy/mm/dd'),40 from dual union all
10 select to_date('2011/05/20','yyyy/mm/dd'),10 from dual union all
11 select to_date('2011/06/17','yyyy/mm/dd'),20 from dual union all
12 select to_date('2011/06/18','yyyy/mm/dd'),30 from dual)
13 ,tb as(
14 select '2011/04' month,sum(val) sum_sal
15 from t
16 where dt between to_date('2011/03/16','yyyy/mm/dd')
17 and to_date('2011/03/16','yyyy/mm/dd')+30
18 union all
19 select '2011/05',sum(val)
20 from t
21 where dt between to_date('2011/04/16','yyyy/mm/dd')
22 and to_date('2011/04/16','yyyy/mm/dd')+30
23 union all
24 select '2011/06',sum(val)
25 from t
26 where dt between to_date('2011/05/16','yyyy/mm/dd')
27 and to_date('2011/05/16','yyyy/mm/dd')+30
28 union all
29 select '2011/07',sum(val)
30 from t
31 where dt between to_date('2011/06/16','yyyy/mm/dd')
32 and to_date('2011/06/16','yyyy/mm/dd')+30)
33 select * from tb
34 /
MONTH SUM_SAL
------- ----------
2011/04 150
2011/05 100
2011/06 10
2011/07 50
--关键是根据你开始时间和结束时间产生一个临时表(每个周期)
drop table test;create table test as
with tb 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 * from tb--SQL语句:with t as( --开始时间和结束时间
select '2011/03/16' stime, '2011/09/15' etime from dual
),
tab as( --产生各个周期的临时表
select add_months(stime,level-1) stime, add_months(stime,level)-1 etime
from t
connect by level<=ceil(months_between(etime,stime))
)
select to_char(a.etime,'yyyy/mm') month,
Nvl((select sum(val) from test where dt between a.stime and a.etime),0) total
from tab a--结果:
MONTH TOTAL
----------------
2011/04 150
2011/05 100
2011/06 10
2011/07 50
2011/08 0
2011/09 0
--这样写好看点:
select to_char(a.etime,'yyyy/mm') month,sum(val) total
from tab a , test b
where b.dt between a.stime and a.etime
group by to_char(a.etime,'yyyy/mm')MONTH TOTAL
----------------
2011/04 150
2011/05 100
2011/06 10
2011/07 50
2 from t;DONE_DATE MONEY
----------- ----------
2011/3/16 20
2011/3/17 30
2011/4/14 50
2011/4/15 50
2011/4/16 10
2011/4/17 20
2011/5/14 30
2011/5/15 40
2011/5/20 10
2011/6/17 20
2011/6/18 3011 rows selectedSQL>
SQL> SELECT to_char(add_months(date'2011-4-1',
2 trunc(
3 months_between(done_date,DATE '2011-03-16')
4 )),'yyyy/mm') mon
5 ,SUM(money) sum_money
6 FROM t
7 GROUP BY trunc(
8 months_between(done_date,DATE '2011-03-16')
9 )
10 ORDER BY mon;MON SUM_MONEY
------- ----------
2011/04 150
2011/05 100
2011/06 10
2011/07 50
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作为显示数据