如何返回60组年月数据？基于参数的SQL表值函数开发需求

Got it, let's build this function using a recursive CTE (Common Table Expression) — it's perfect for generating sequential date ranges in reverse. Here's a complete implementation that handles both the 60-month limit and the 2005 cutoff:

CREATE FUNCTION dbo.GetLast60Months(@month INT, @year INT)
RETURNS TABLE
AS
RETURN
(
    WITH RecursiveMonths AS (
        -- Anchor member: start with the input year and month
        SELECT 
            Year = @year,
            Month = @month,
            MonthCounter = 1
        UNION ALL
        -- Recursive member: generate the previous month
        SELECT 
            Year = CASE WHEN rm.Month = 1 THEN rm.Year - 1 ELSE rm.Year END,
            Month = CASE WHEN rm.Month = 1 THEN 12 ELSE rm.Month - 1 END,
            MonthCounter = rm.MonthCounter + 1
        FROM RecursiveMonths rm
        -- Stop when we've hit 60 months OR the next year would be before 2005
        WHERE rm.MonthCounter < 60
          AND CASE WHEN rm.Month = 1 THEN rm.Year - 1 ELSE rm.Year END >= 2005
    )
    SELECT 
        Year,
        Month,
        -- Optional: return a formatted YYYY-MM string if needed
        FormattedYearMonth = CONCAT(Year, '-', RIGHT('0' + CAST(Month AS VARCHAR(2)), 2))
    FROM RecursiveMonths
);

Key Details Explained: #

• Recursive CTE Structure: The anchor member starts with your input @year and @month. The recursive member calculates the previous month: if we're at January (month 1), we roll back to December of the prior year; otherwise, we just decrement the month.
• Dual Termination Conditions: We stop generating rows when either:
  1. We've created 60 months of data (MonthCounter < 60), or
  2. The next month would fall into a year before 2005 (using the CASE statement to check what the next year would be).
• Optional Formatted Output: The FormattedYearMonth column gives you a clean YYYY-MM string, which is often handy for reporting or joining to other date tables.

Example Usage: #

If you call the function with @month=1 and @year=2018:

SELECT * FROM dbo.GetLast60Months(1, 2018);

This returns 60 rows starting from 2018-01 and going back to 2013-01 (since 2013 is well above the 2005 cutoff).

If you call it with @month=6 and @year=2007:

SELECT * FROM dbo.GetLast60Months(6, 2007);

This only returns 31 rows (from 2007-06 back to 2005-01) because hitting 2005 stops the recursion before we reach 60 months.

Quick Notes: #

• You can add input validation if needed (e.g., check that @month is 1-12 and @year is a 4-digit number) by adding IF statements at the start of the function or a WHERE clause in the anchor member.
• If your SQL Server version supports it, a numbers table could replace recursion, but recursion is more straightforward here for reverse date ranges.

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