SQL Server：按日期拆分单行数据为多行并分配金额

需求说明 #

将一条覆盖多个日期的汇总记录（如Mathew3天总计赚取600美元）拆分为每日单独的记录，每日金额为总金额除以天数，同时将每日的start_date和end_date都设置为当天日期。

原始数据 #

id  name    start_date  end_date     Total_Dollars
---------------------------------------------------
1   Mathew  01/01/2021  03/01/2021   600

期望输出 #

id  name    start_date  end_date     Total_Dollars
--------------------------------------------------
1   Rahul   01/01/2021  01/01/2021   200
1   Rahul   02/01/2021  02/01/2021   200
1   Rahul   03/01/2021  03/01/2021   200

注：期望输出中姓名为Rahul应为笔误，若需保留原姓名可替换代码中的对应字段

SQL解决方案 #

我们可以使用**递归CTE（公共表表达式）**来生成start_date到end_date之间的所有日期，再关联原始表计算每日金额：

WITH DateSequence AS (
    -- 递归起始点：取原始记录的start_date
    SELECT 
        id,
        name,
        start_date AS current_date,
        end_date,
        Total_Dollars,
        -- 计算每日金额：总金额除以天数（DATEDIFF计算日期差+1得到总天数）
        Total_Dollars / (DATEDIFF(day, start_date, end_date) + 1) AS Daily_Dollars
    FROM YourTableName
    UNION ALL
    -- 递归生成后续日期
    SELECT 
        id,
        name,
        DATEADD(day, 1, current_date) AS current_date,
        end_date,
        Total_Dollars,
        Daily_Dollars
    FROM DateSequence
    WHERE current_date < end_date
)
-- 输出最终结果，将current_date同时作为start_date和end_date
SELECT 
    id,
    -- 若需保留原姓名Mathew，替换为name即可
    'Rahul' AS name,
    current_date AS start_date,
    current_date AS end_date,
    Daily_Dollars AS Total_Dollars
FROM DateSequence
ORDER BY current_date;

代码说明 #

1. 递归CTE DateSequence：
   • 初始查询获取原始记录的基础信息，并计算每日金额（总金额除以总天数，DATEDIFF(day, start_date, end_date)+1是因为包含起始和结束日期）。
   • 递归部分每次将日期加1，直到达到end_date。
2. 最终查询：将生成的每日日期同时作为start_date和end_date，并输出每日金额；若期望输出中的姓名是笔误，直接使用原表的name字段即可。
3. 若原始表有多个类似的跨天记录，该代码也能批量处理。

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