oracle 获取某一个时间段内中间含有哪几个月

(1)不含开始日期，含结束日期

SELECT to_char(add_months(to_date(SUBSTR('2016-11-01',0,7),'yyyy-mm'),ROWNUM),'YYYY-MM')AS vc_month

FROM all_objects WHERE ROWNUM <=

(SELECT months_between(to_date(SUBSTR('2017-11-31',0,7),'YYYY-MM'),to_date(SUBSTR('2016-11-01',0,7),'YYYY-MM'))  FROM dual);

效果

(2)含开始日期，不含结束日期

SELECT to_char(add_months(to_date(SUBSTR('2016-11-01',0,7),'yyyy-mm'),ROWNUM-1),'YYYY-MM')AS vc_month

FROM all_objectsWHERE ROWNUM <=

(SELECT months_between(to_date(SUBSTR('2017-11-31',0,7),'YYYY-MM'),to_date(SUBSTR('2016-11-01',0,7),'YYYY-MM'))FROM dual)

效果

(3) 含开始日期，含结束日期

SELECT to_char(add_months(to_date(SUBSTR('2016-11-01',0,7),'yyyy-mm'),ROWNUM-1),'YYYY-MM')AS vc_month

FROM all_objects WHERE ROWNUM <=

(SELECT months_between(to_date(SUBSTR('2017-11-31',0,7),'YYYY-MM'),to_date(SUBSTR('2016-11-01',0,7),'YYYY-MM'))+1FROM dual)

结果

(4)不含开始日期，结束日期多一个月

SELECT to_char(add_months(to_date(SUBSTR('2016-11-01',0,7),'yyyy-mm'),ROWNUM),'YYYY-MM')AS vc_month

FROM all_objectsWHERE ROWNUM <=

(SELECT months_between(to_date(SUBSTR('2017-11-31',0,7),'YYYY-MM'),to_date(SUBSTR('2016-11-01',0,7),'YYYY-MM'))+1FROM dual)

结果