SQL Server按上线日期拆分年度销售额为月度明细的实现方法

完全可以实现，用递归CTE生成连续月份序列关联原表即可完成，不需要额外创建物理表。

实现思路 #

• 先生成1-24的连续数字序列，对应项目上线后前24个月的序号
• 将原销售表的每条项目记录和24个序号做笛卡尔连接，为每个项目生成24条待计算的月度记录
• 按序号计算对应销售月份：用DATEADD函数将项目上线日期加（序号-1）个月，取当月首日作为销售月份
• 匹配月度销售额：序号为1-12时用Sales Y1除以12，序号为13-24时用Sales Y2除以12，可按需保留小数精度

示例代码 #

先假设你的销售表名为sales_project，结构如下：

对应的查询代码如下：

WITH num_seq AS (
    -- 递归生成1-24的连续序号
    SELECT 1 AS month_num
    UNION ALL
    SELECT month_num + 1 FROM num_seq WHERE month_num < 24
)
SELECT
    p.project_name,
    -- 计算销售月份（取当月首日，也可以用EOMONTH取当月最后一天）
    DATEFROMPARTS(YEAR(DATEADD(MONTH, n.month_num - 1, p.launch_date)), MONTH(DATEADD(MONTH, n.month_num - 1, p.launch_date)), 1) AS sales_month,
    -- 计算月度销售额，保留2位小数
    CASE
        WHEN n.month_num <=12 THEN CAST(p.sales_y1 / 12 AS DECIMAL(18,2))
        ELSE CAST(p.sales_y2 / 12 AS DECIMAL(18,2))
    END AS monthly_sales
FROM sales_project p
CROSS JOIN num_seq n
-- 如果需要排除Y2为0或者没有Y2数据的项目，可以加WHERE条件
-- WHERE p.sales_y2 > 0
ORDER BY p.project_name, sales_month

补充说明 #

• 如果需要避免平摊时的四舍五入误差，可以调整最后一个月的销售额：比如前11个月按平摊值计算，第12个月用年度总额减去前11个月的总和
• 若后续需要扩展到3年及以上的平摊，只要修改递归CTE的最大序号，再新增对应年度的销售额匹配逻辑即可
• 如果你的SQL Server版本低于2012，没有DATEFROMPARTS函数，可以改用CAST(CAST(YEAR(...) AS VARCHAR)+'-'+CAST(MONTH(...) AS VARCHAR)+'-01' AS DATE)的方式计算当月首日

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