基于起始日期与结束日期生成日期列表的SQL实现方法

Got it, let's figure out how to turn that single record with a start and end date into a list of monthly entries matching your desired format. The core idea is to generate a sequence of months between your start and end dates, then pair each month with the original ID. Here's how to do this across popular SQL dialects:

MySQL (8.0+) #

Use a recursive CTE to generate each month starting from the first day of your start date's month, up to the first day of your end date's month. Then format the date to get the "Mon YYYY" style:

WITH RECURSIVE monthly_dates AS (
    SELECT 
        id,
        DATE_FORMAT(`Starting date`, '%Y-%m-01') AS month_start,
        `Ending date`
    FROM your_table
    UNION ALL
    SELECT 
        id,
        DATE_ADD(month_start, INTERVAL 1 MONTH),
        `Ending date`
    FROM monthly_dates
    WHERE month_start <= DATE_FORMAT(`Ending date`, '%Y-%m-01')
)
SELECT 
    id,
    DATE_FORMAT(month_start, '%b %Y') AS `Date`
FROM monthly_dates
ORDER BY month_start;

PostgreSQL #

PostgreSQL has a handy generate_series function that makes this straightforward. We'll truncate both dates to their first day of the month, then generate all months in between:

SELECT 
    t.id,
    TO_CHAR(m.month_date, 'Mon YYYY') AS "Date"
FROM your_table t
CROSS JOIN generate_series(
    DATE_TRUNC('month', t."Starting date"),
    DATE_TRUNC('month', t."Ending date"),
    INTERVAL '1 month'
) AS m(month_date)
ORDER BY m.month_date;

SQL Server #

You can use a recursive CTE here too, or leverage DATEFROMPARTS to build the first day of each month. For formatting, use FORMAT (if you're on SQL Server 2012+) or DATENAME for older versions:

With FORMAT (2012+) #

WITH monthly_dates AS (
    SELECT 
        id,
        DATEFROMPARTS(YEAR([Starting date]), MONTH([Starting date]), 1) AS month_start,
        [Ending date]
    FROM your_table
    UNION ALL
    SELECT 
        id,
        DATEADD(MONTH, 1, month_start),
        [Ending date]
    FROM monthly_dates
    WHERE month_start <= DATEFROMPARTS(YEAR([Ending date]), MONTH([Ending date]), 1)
)
SELECT 
    id,
    FORMAT(month_start, 'MMM yyyy') AS [Date]
FROM monthly_dates
ORDER BY month_start;

For Older SQL Server Versions #

WITH monthly_dates AS (
    SELECT 
        id,
        DATEFROMPARTS(YEAR([Starting date]), MONTH([Starting date]), 1) AS month_start,
        [Ending date]
    FROM your_table
    UNION ALL
    SELECT 
        id,
        DATEADD(MONTH, 1, month_start),
        [Ending date]
    FROM monthly_dates
    WHERE month_start <= DATEFROMPARTS(YEAR([Ending date]), MONTH([Ending date]), 1)
)
SELECT 
    id,
    DATENAME(MONTH, month_start) + ' ' + CAST(YEAR(month_start) AS VARCHAR(4)) AS [Date]
FROM monthly_dates
ORDER BY month_start;

Quick Notes #

• If your column names have spaces (like Starting date), make sure to wrap them in backticks (MySQL), double quotes (PostgreSQL), or square brackets (SQL Server) to avoid syntax errors.
• These queries work for multiple records too—each row in your original table will generate its own list of monthly entries.
• The ORDER BY ensures the months are returned in chronological order, which matches your desired output.

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