SQL Server如何列出当前月份起过去10年的所有月份？

你可以通过生成连续的月份偏移量，或者结合年份与月份的交叉连接来实现需求，以下是两种可行的方案：

方案一：直接生成120个月的偏移量 #

过去10年共120个月，我们可以先生成0到119的数字序列，每个数字代表从当前月份往前推的月数，再通过DATEADD计算对应月份：

WITH MonthOffsets AS (
    -- 生成0到119的偏移值，交叉连接sys.objects确保有足够行数
    SELECT TOP 120 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS OffsetMonths
    FROM sys.objects s1
    CROSS JOIN sys.objects s2
)
SELECT 
    -- 格式化日期为yyyyMM格式，截断到当月第一天避免时间干扰
    FORMAT(DATEADD(MONTH, -OffsetMonths, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)), 'yyyyMM') AS YearMonth
FROM MonthOffsets
ORDER BY YearMonth DESC;

方案二：年份与月份交叉连接 #

先生成过去10年的每个年度的当前月份，再为每个年份生成12个月份的偏移，通过交叉连接组合所有月份：

WITH Years AS (
    -- 生成过去10年的年度当前月份（截断到当月第一天）
    SELECT TOP 10 
        DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(YEAR, - (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1), GETDATE())), 0) AS YearMonthStart
    FROM sys.objects
),
Months AS (
    -- 生成0到11的月份偏移值，对应一年的12个月
    SELECT TOP 12 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS MonthOffset
    FROM sys.objects
)
SELECT 
    FORMAT(DATEADD(MONTH, MonthOffset, YearMonthStart), 'yyyyMM') AS YearMonth
FROM Years
CROSS JOIN Months
ORDER BY YearMonth DESC;

说明 #

• 两种方案都使用sys.objects作为行生成器，若数据库中该表行数不足，可替换为其他包含足够数据的系统表，或自定义数字表。
• DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)用于将当前日期截断到当月第一天，避免时间部分对结果的干扰。
• 最终结果按YearMonth降序排列，从当前月份开始往前展示10年的所有月份。

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