求每个业务员从2007年以后每个月的销售额
月份不能按照正常月份 现在定义:
1月 1.1-1.25
2月 1.26-2.25
3月 2.26-3.25
4月 3.26-4.25
5月 4.26-5.25
6月 5.26-6.25
7月 6.26-7.25
8月 7.26-8.25
9月 8.26-9.25
10月 9.26-10.25
11 月 10.26-11.25
12 月 11.26-12.31
ps:而且要求不能是死数据,要求必须为活表 (即:当有2017年数据时自动生成各业务员2017年各月份销售总额)
谢谢大家了 给个思路,谢谢
月份不能按照正常月份 现在定义:
1月 1.1-1.25
2月 1.26-2.25
3月 2.26-3.25
4月 3.26-4.25
5月 4.26-5.25
6月 5.26-6.25
7月 6.26-7.25
8月 7.26-8.25
9月 8.26-9.25
10月 9.26-10.25
11 月 10.26-11.25
12 月 11.26-12.31
ps:而且要求不能是死数据,要求必须为活表 (即:当有2017年数据时自动生成各业务员2017年各月份销售总额)
谢谢大家了 给个思路,谢谢
select 'a' as uname,100 as je,date'2015-01-10' as rq from dual
union all
select 'a' as uname,200 as je,date'2015-01-20' as rq from dual
union all
select 'a' as uname,300 as je,date'2015-01-26' as rq from dual
union all
select 'a' as uname,100 as je,date'2015-02-10' as rq from dual
union all
select 'a' as uname,500 as je,date'2015-02-25' as rq from dual
union all
select 'a' as uname,100 as je,date'2015-04-10' as rq from dual
union all
select 'b' as uname,400 as je,date'2015-02-10' as rq from dual
union all
select 'b' as uname,100 as je,date'2015-04-10' as rq from dual
union all
select 'b' as uname,100 as je,date'2015-05-20' as rq from dual
union all
select 'b' as uname,100 as je,date'2015-06-10' as rq from dual
union all
select 'b' as uname,600 as je,date'2015-06-30' as rq from dual
union all
select 'b' as uname,700 as je,date'2015-07-10' as rq from dual
union all
select 'b' as uname,800 as je,date'2015-08-14' as rq from dual
union all
select 'b' as uname,900 as je,date'2015-09-10' as rq from dual
union all
select 'b' as uname,1000 as je,date'2015-10-26' as rq from dual
)
select uname,to_char(add_months(rq-25,1),'yyyymm'),sum(je)
from t
group by uname,to_char(add_months(rq-25,1),'yyyymm')
order by uname,to_char(add_months(rq-25,1),'yyyymm')
from t , m
where t.rq between m.start and m.end
select rownum form dual connect by rownum<n
,具体写法楼主自己发掘吧
2月 1.26-2.25
3月 2.26-3.25
4月 3.26-4.25
5月 4.26-5.25
6月 5.26-6.25
7月 6.26-7.25
8月 7.26-8.25
9月 8.26-9.25
10月 9.26-10.25
11 月 10.26-11.25
12 月 11.26-12.31
首先将它查个表来,这表是死的吧,设字段有月份、开始日期、结束日期,with tmp as(..............)
然后再将人员销售额的表跟这个表关联,销售日期的月份跟上面的结束日期的月份可以关联,
select t.人员,tmp.月份,sum(case when from t .date>tmp.开始日期 and t.date<tmp.结束日期 then t.销售额 else 0)
left join tmp on extract(month from t.date)=extract(month from tmp.结束日期)
group by t.人员,tmp.月份