如何编写生成动态月份年份行的SQL查询？

我的源表数据如下：

pos_id start_date Enddate
1 20140131 20141201
2 20150331 20151201

期望得到的结果是每个职位在起始日期到结束日期之间的每个月份都生成一行，包含对应月份和年份：

position_id startdate Enddate Month Year
1 20140131 20141201 1 2014
1 20140131 20141201 2 2014
1 20140131 20141201 3 2014
1 20140131 20141201 4 2014
1 20140131 20141201 5 2014
1 20140131 20141201 6 2014
1 20140131 20141201 7 2014
1 20140131 20141201 8 2014
1 20140131 20141201 9 2014
1 20140131 20141201 10 2014
1 20140131 20141201 11 2014
1 20140131 20141201 12 2014
2 20150331 20151201 3 2015
2 20150331 20151201 4 2015
2 20150331 20151201 5 2015
2 20150331 20151201 6 2015
2 20150331 20151201 7 2015
2 20150331 20151201 8 2015
2 20150331 20151201 9 2015
2 20150331 20151201 10 2015
2 20150331 20151201 11 2015
2 20150331 20151201 12 2015

我已经能通过以下代码实现日期月份值的比较，但不知道如何生成上述期望结果中的动态月份和年份行，恳请赐教：

select start_date, cast(SUBSTRING(start_date,5,2)as int),end_date,cast(SUBSTRING(end_date,5,2)as int),
case when cast(SUBSTRING(start_date,5,2)as int) < cast(SUBSTRING(end_date,5,2)as int) then start_date end as date_range
from [dbo].[SRC_TEST_DATE]

要生成每个时间段内的月度行，最常用的方法是使用递归CTE（公共表表达式），或者借助一个数字辅助表。下面提供递归CTE的解决方案，不需要额外创建表，适用性更广：

解决方案：递归CTE生成月度序列 #

WITH DateRangeCTE AS (
    -- 锚点成员：获取每个pos_id的起始月份和年份
    SELECT 
        pos_id AS position_id,
        start_date,
        Enddate,
        -- 提取起始月份并转为整数
        CAST(SUBSTRING(start_date, 5, 2) AS INT) AS current_month,
        -- 提取起始年份并转为整数
        CAST(SUBSTRING(start_date, 1, 4) AS INT) AS current_year
    FROM [dbo].[SRC_TEST_DATE]
    
    UNION ALL
    
    -- 递归成员：逐月递增，直到达到结束月份
    SELECT 
        position_id,
        start_date,
        Enddate,
        -- 如果当前月份是12，下一个月份重置为1，否则+1
        CASE WHEN current_month = 12 THEN 1 ELSE current_month + 1 END,
        -- 如果当前月份是12，年份+1，否则保持原年份
        CASE WHEN current_month = 12 THEN current_year + 1 ELSE current_year END
    FROM DateRangeCTE
    -- 终止条件：当前年月不超过结束日期的年月
    WHERE 
        DATEFROMPARTS(current_year, current_month, 1) <= DATEFROMPARTS(
            CAST(SUBSTRING(Enddate, 1, 4) AS INT),
            CAST(SUBSTRING(Enddate, 5, 2) AS INT),
            1
        )
)
-- 最终查询：整理输出需要的字段
SELECT 
    position_id,
    start_date AS startdate,
    Enddate,
    current_month AS [Month],
    current_year AS [Year]
FROM DateRangeCTE
ORDER BY position_id, current_year, current_month;

代码解释： #

1. 锚点成员：先从源表中获取每个职位的基础信息，同时提取起始日期的月份和年份作为递归的起点。
2. 递归成员：每次递归都将月份加1，如果是12月则重置为1月并年份加1，直到当前年月不超过结束日期的年月。
3. 终止条件：通过DATEFROMPARTS把年月转换成标准日期，确保递归不会超过结束日期所在的月份。
4. 最终输出：调整字段名并按职位、年份、月份排序，得到你需要的结果。

注意事项： #

• 如果你的SQL Server版本早于2012（不支持DATEFROMPARTS），可以用CAST(SUBSTRING(start_date,1,4) + '-' + SUBSTRING(start_date,5,2) + '-01' AS DATE)来转换日期。
• 如果数据量很大（比如上千个职位且时间段很长），递归CTE可能性能不如数字辅助表，这时可以预先创建一个包含1-120（覆盖10年）的数字表，再通过交叉连接生成序列。

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