如何在单表中基于多行起止日期生成多日历序列？

嘿，这个场景我之前也碰到过！你之前的查询只支持单行，是因为没有用批量的日期序列生成逻辑和原始表做关联。下面我给你拆解思路，再针对几种常见数据库给出具体代码：

核心思路 #

1. 先生成一个覆盖所有需要的月份的序列（从你原始表中最早的Start_date到最晚的End_date的所有月份第一天）；
2. 把这个序列和你的原始表做关联，筛选出每个key对应的、落在Start_date和End_date范围内的月份；
3. 最后把生成的月份格式化成你需要的dd-mm-yyyy样式。

针对不同数据库的实现代码 #

1. SQL Server（2012+版本） #

用系统表生成足够多的数字序列，再转换成月份：

WITH MonthSequence AS (
    -- 生成1000个月份的序列，足够覆盖绝大多数场景
    SELECT TOP (1000) 
        DATEADD(MONTH, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, '1900-01-01') AS MonthStart
    FROM sys.all_columns
)
SELECT 
    t.[key],
    -- 格式化日期为dd-mm-yyyy格式
    FORMAT(DATEADD(MONTH, DATEDIFF(MONTH, '1900-01-01', ms.MonthStart), CONVERT(DATE, t.Start_date, 103)), 'dd-MM-yyyy') AS GeneratedMonth
FROM YourOriginalTable t
JOIN MonthSequence ms 
    -- 匹配每个key的月份范围：把Start/End_date转成当月第一天做比较
    ON ms.MonthStart >= DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(DATE, t.Start_date, 103)), 0)
    AND ms.MonthStart <= DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(DATE, t.End_date, 103)), 0)
ORDER BY t.[key], GeneratedMonth;

注：CONVERT(DATE, t.Start_date, 103)是把dd-mm-yyyy格式的字符串转成SQL Server的日期类型，避免格式错误。

2. MySQL（8.0+版本，支持递归CTE） #

用递归CTE生成月份序列：

WITH RECURSIVE MonthSequence AS (
    -- 从原始表最早的Start_date当月第一天开始
    SELECT MIN(STR_TO_DATE(Start_date, '%d-%m-%Y')) AS MonthStart
    FROM YourOriginalTable
    UNION ALL
    -- 逐月递增
    SELECT DATE_ADD(MonthStart, INTERVAL 1 MONTH)
    FROM MonthSequence
    -- 生成到原始表最晚的End_date当月第一天
    WHERE MonthStart <= (SELECT MAX(STR_TO_DATE(End_date, '%d-%m-%Y')) FROM YourOriginalTable)
)
SELECT 
    t.`key`,
    -- 格式化成dd-mm-yyyy
    DATE_FORMAT(ms.MonthStart, '%d-%m-%Y') AS GeneratedMonth
FROM YourOriginalTable t
JOIN MonthSequence ms 
    ON ms.MonthStart >= DATE_FORMAT(STR_TO_DATE(t.Start_date, '%d-%m-%Y'), '%Y-%m-01')
    AND ms.MonthStart <= DATE_FORMAT(STR_TO_DATE(t.End_date, '%d-%m-%Y'), '%Y-%m-01')
ORDER BY t.`key`, GeneratedMonth;

3. PostgreSQL #

PostgreSQL自带的generate_series函数可以直接生成日期序列，非常方便：

SELECT 
    t.key,
    -- 格式化成dd-mm-yyyy
    TO_CHAR(generate_series(
        DATE_TRUNC('month', TO_DATE(t.Start_date, 'DD-MM-YYYY')),
        DATE_TRUNC('month', TO_DATE(t.End_date, 'DD-MM-YYYY')),
        '1 month'::interval
    ), 'dd-mm-yyyy') AS GeneratedMonth
FROM YourOriginalTable t
ORDER BY t.key, GeneratedMonth;

为什么之前的单行查询失效？ #

你之前的逻辑应该是针对单个key硬编码了日期范围，没有把序列和所有key的日期范围做关联匹配。上面的方法通过先生成全局的月份序列，再和每个key的日期区间做JOIN，就能批量处理所有行啦！

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