Oracle/Mysql查询最近N个月写法

最新推荐文章于 2023-08-17 09:45:10 发布

cs_19_dn

最新推荐文章于 2023-08-17 09:45:10 发布

阅读量226

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/cs_19_dn/article/details/100145422

版权

java 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

--oracle 未来12个月
SELECT TO_CHAR(ADD_MONTHS(ADD_MONTHS(SYSDATE, 0), ROWNUM - 1),
               'YYYY-MM') AS YEARMONTH
               FROM ALL_OBJECTS
               WHERE ROWNUM <= 12;

--oracle 前12个月
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 1 - ROWNUM),
               'YYYY-MM') AS YEARMONTH
               FROM ALL_OBJECTS
               WHERE ROWNUM <= 12;

--mysql 前12个月
SELECT DATE_FORMAT(
                                       date_add(NOW(), interval 1-(@rownum :=@rownum + 1) MONTH),
               '%Y-%m') AS YEARMONTH
               FROM information_schema.tables t1,(SELECT @rownum := 0) t2
limit 12;

--mysql 未来12个月
SELECT DATE_FORMAT(
                                       date_add(NOW(), interval (@rownum :=@rownum + 1) -1 MONTH),
               '%Y-%m') AS YEARMONTH
               FROM information_schema.tables t1,(SELECT @rownum := 0) t2
limit 12;