MySQL获取当前日期前N个月的月份列表的递归写法

最新推荐文章于 2024-04-22 13:24:23 发布

别跟我比懒我懒得跟你比

最新推荐文章于 2024-04-22 13:24:23 发布

阅读量1k

点赞数 3

分类专栏：数据库 # MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/ziye1985/article/details/126029353

版权

数据库同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

MySQL

1 篇文章 0 订阅

订阅专栏

同事问了一个关于获取当前日期前一年的月份列表的问题，给了一个SQL，问有没有更简单的写法，其实我也不是很懂，用代码的思路想想应该也就是用循环或者递归方法来实现，但涉及到具体的，还是查了下资料。

先把同事给的SQL贴出来，查了下网上好像很多都是这种写法。

SELECT months from (
   SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS 'months' 
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS 'months' 
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS 'months' 
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS 'months' 
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS 'months' 
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS 'months' 
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS 'months' 
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS 'months' 
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS 'months' 
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS 'months' 
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS 'months'
   UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 12 MONTH), '%Y-%m') AS 'months'
 )t;

下面是根据MySQL8.0后的CTE写法改的递归写法

with RECURSIVE months(months,num) as (
	SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS 'months' , 1 as num
	union all
	SELECT DATE_FORMAT((CURDATE() - INTERVAL num+1 MONTH), '%Y-%m') AS 'months', num+1 as num from months where num<12
)
select months from months ;

记录一下，防止以后忘了