oracle 怎么根据自定义月份统计各月数据,例如每月结账时间为上月26号到本月25号,统计每月营业额
解决方案 »
- 急招ORACLE高手,优化人员!
- oracle 10g用企业管理器怎么查看和编辑表中的内容啊
- 请问:关于oracle有没有好的一些参考资料啊,感觉很好东西都拿不准.
- 问个小问题,请高手解答!!!!!
- 为什么建立分区了,select * from table_name partition(part_name)还是全表扫描呢?
- 有什么简单的语句可以将竖表转成横表?(详见例子)
- 为什么我的异常处理在循环内编译不了。在循环外能顺利通过?
- 日期问题:to_date()函数
- 求一条SQL语句
- oracle常见错误问题!
- [求助]oracle client 12.1卸载
- 非常奇怪的 12152 错误 求大神关注
with mon_yye
as
(
select to_date('20160125','yyyymmdd') date1,100 yye from dual union all
select to_date('20160126','yyyymmdd') ,200 from dual union all
select to_date('20160131','yyyymmdd') ,200 from dual union all
select to_date('20160225','yyyymmdd') ,300 from dual union all
select to_date('20160226','yyyymmdd') ,400 from dual union all
select to_date('20160227','yyyymmdd') ,500 from dual union all
select to_date('20160228','yyyymmdd') ,500 from dual union all
select to_date('20160229','yyyymmdd') ,500 from dual union all
select to_date('20160326','yyyymmdd') ,600 from dual )
select to_char(add_months(a.date1 - 25, 1), 'YYYYMM') date2, sum(a.yye) yye
from mon_yye a
group by to_char(add_months(a.date1 - 25, 1), 'YYYYMM')
order by to_char(add_months(a.date1 - 25, 1), 'YYYYMM')
select add_months(to_date('2017-01-25','yyyy-mm-dd'),1) from dual
返回2017-02-25按照你的需求,那就是统计日期 between to_date('2017-01-25','yyyy-mm-dd') and add_months(to_date('2017-01-25','yyyy-mm-dd'),1)
as
(
select to_date('20160125','yyyymmdd') date1,100 yye from dual union all
select to_date('20160126','yyyymmdd') ,200 from dual union all
select to_date('20160131','yyyymmdd') ,200 from dual union all
select to_date('20160225','yyyymmdd') ,300 from dual union all
select to_date('20160226','yyyymmdd') ,400 from dual union all
select to_date('20160227','yyyymmdd') ,500 from dual union all
select to_date('20160228','yyyymmdd') ,500 from dual union all
select to_date('20160229','yyyymmdd') ,500 from dual union all
select to_date('20160326','yyyymmdd') ,600 from dual )
select t.month_s,SUM(t.yye) from (
select
case when a.date1 between a.date1 and add_months(a.date1,1)+1
then to_char(add_months(a.date1,1),'YYYYMM')
end month_s --如果时间在26到下月25区间内,则取出月份
,a.yye from mon_yye a) t
group by t.month_s
order by t.month_s;
as
(
select to_date('20160120','yyyymmdd') date1,100 yye from dual union all
select to_date('20160125','yyyymmdd') date1,100 from dual union all
select to_date('20160126','yyyymmdd') ,200 from dual union all
select to_date('20160131','yyyymmdd') ,200 from dual union all
select to_date('20160225','yyyymmdd') ,300 from dual union all
select to_date('20160226','yyyymmdd') ,400 from dual union all
select to_date('20160227','yyyymmdd') ,500 from dual union all
select to_date('20160228','yyyymmdd') ,500 from dual union all
select to_date('20160229','yyyymmdd') ,500 from dual union all
select to_date('20160326','yyyymmdd') ,600 from dual )select t.months,sum(t.yye) from (
select case when substr(to_char(a.date1,'YYYYMMDD'),7,2) >=26 then
to_char(add_months(a.date1,1),'YYYYMM')
else to_char(a.date1,'YYYYMM')
end months, --如果日期大于等于26则月份加1,反之保持不变
substr(to_char(a.date1,'YYYYMMDD'),7,2) ,a.date1,a.yye from mon_yye a
) t
group by t.months
order by t.months