时间 between to_date(to_char(sysdate,'yyyy')-1||'-1-1 0:0:0','yyyy-mm-dd hh24:mi:ss') and sysdate,后面按年月group by一下
select to_char(add_months(to_date('2016' || '1231', 'yyyy-mm-dd'), -level + 1), --从2016年年底往上计算24个月 'YYYY-MM') mon from dual connect by level <= 24 --2年24个月 order by mon;
select '2015-01' mon from dual union all select '2015-02' mon from dual union all select '2015-03' mon from dual union all select '2015-04' mon from dual union all select '2015-05' mon from dual union all select '2015-06' mon from dual union all select '2015-07' mon from dual union all select '2015-08' mon from dual union all select '2015-09' mon from dual union all select '2015-10' mon from dual union all select '2015-11' mon from dual union all select '2015-12' mon from dual union all select '2016-01' mon from dual union all select '2016-02' mon from dual union all select '2016-03' mon from dual union all select '2016-04' mon from dual union all select '2016-05' mon from dual union all select '2016-06' mon from dual union all select '2016-07' mon from dual union all select '2016-08' mon from dual union all select '2016-09' mon from dual union all select '2016-10' mon from dual union all select '2016-11' mon from dual union all select '2016-12' mon from dual order by 1;
-level + 1), --从2016年年底往上计算24个月
'YYYY-MM') mon
from dual
connect by level <= 24 --2年24个月
order by mon;
select '2015-01' mon from dual
union all
select '2015-02' mon from dual
union all
select '2015-03' mon from dual
union all
select '2015-04' mon from dual
union all
select '2015-05' mon from dual
union all
select '2015-06' mon from dual
union all
select '2015-07' mon from dual
union all
select '2015-08' mon from dual
union all
select '2015-09' mon from dual
union all
select '2015-10' mon from dual
union all
select '2015-11' mon from dual
union all
select '2015-12' mon from dual
union all
select '2016-01' mon from dual
union all
select '2016-02' mon from dual
union all
select '2016-03' mon from dual
union all
select '2016-04' mon from dual
union all
select '2016-05' mon from dual
union all
select '2016-06' mon from dual
union all
select '2016-07' mon from dual
union all
select '2016-08' mon from dual
union all
select '2016-09' mon from dual
union all
select '2016-10' mon from dual
union all
select '2016-11' mon from dual
union all
select '2016-12' mon from dual
order by 1;