如何在SQL中动态生成过去36个月的日期列表

嘿，这个需求我之前帮不少开发者处理过！动态生成过去36个月的日期列表，核心思路是借助递归逻辑或者数据库内置的日期生成工具，不同SQL方言的具体实现略有差异，下面给你整理几种主流数据库的可行方案：

MySQL/MariaDB 实现 #

可以用递归CTE（Common Table Expression）生成数字序列，再结合日期函数计算每个月的起止日期：

WITH RECURSIVE months AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n + 1 FROM months WHERE n < 35
)
SELECT 
    DATE_FORMAT(date_sub(curdate(), interval n month), '%Y-%m-01') AS month_start,
    LAST_DAY(date_sub(curdate(), interval n month)) AS month_end
FROM months
ORDER BY month_start DESC;

这里递归CTE先生成0到35的数字序列，每个数字对应往前推n个月的偏移量；date_sub计算基准日期，DATE_FORMAT格式化到每月第一天，LAST_DAY直接拿到每月最后一天。如果只需要单一日期（比如每月第一天），删掉多余列即可。

PostgreSQL 实现 #

PostgreSQL自带的generate_series函数能直接生成日期序列，比递归写法更简洁：

方法1：用generate_series快速生成 #

SELECT 
    date_trunc('month', current_date - interval '1 month' * generate_series(0,35))::date AS month_start,
    (date_trunc('month', current_date - interval '1 month' * generate_series(0,35)) + interval '1 month - 1 day')::date AS month_end
ORDER BY month_start DESC;

方法2：递归CTE兼容写法 #

如果偏好通用的递归逻辑，也可以这么写：

WITH RECURSIVE months AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n + 1 FROM months WHERE n < 35
)
SELECT 
    (current_date - (n || ' months')::interval)::date - (extract(day from current_date) - 1)::day AS month_start,
    (current_date - (n || ' months')::interval)::date + (extract(day from (current_date - (n || ' months')::interval + interval '1 month') - interval '1 day') - 1)::day AS month_end
FROM months
ORDER BY month_start DESC;

SQL Server 实现 #

SQL Server支持递归CTE，也可以结合系统内置数字表来实现：

方法1：递归CTE写法 #

WITH RECURSIVE months AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n + 1 FROM months WHERE n < 35
)
SELECT 
    DATEFROMPARTS(YEAR(DATEADD(MONTH, -n, GETDATE())), MONTH(DATEADD(MONTH, -n, GETDATE())), 1) AS month_start,
    EOMONTH(DATEADD(MONTH, -n, GETDATE())) AS month_end
FROM months
ORDER BY month_start DESC;

这里DATEFROMPARTS用来构造每月第一天，EOMONTH是SQL Server专门用来获取月末日期的函数，非常省心。

方法2：利用系统数字表 #

如果你的数据库里有预定义数字表（比如master..spt_values），可以跳过递归：

SELECT TOP 36
    DATEFROMPARTS(YEAR(DATEADD(MONTH, -ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + 1, GETDATE())), 
                  MONTH(DATEADD(MONTH, -ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + 1, GETDATE())), 1) AS month_start,
    EOMONTH(DATEADD(MONTH, -ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + 1, GETDATE())) AS month_end
FROM master..spt_values
ORDER BY month_start DESC;

通用注意事项 #

• 如果你需要从上个月开始往前推36个月（比如当前是2024-05，就到2021-04），只需要把代码里的0改成1，同时把终止条件（比如35）改成36就行。
• 时区敏感场景，记得用数据库的时区函数替换current_date/GETDATE()，比如PostgreSQL的current_timestamp AT TIME ZONE 'UTC'，避免时区偏差。
• 可以根据需求调整日期格式，比如只保留YYYY-MM，用TO_CHAR（PostgreSQL）、DATE_FORMAT（MySQL）或者FORMAT（SQL Server）格式化即可。

内容的提问来源于stack exchange，提问作者Girish