如何在MS-Access SQL中将单条周期记录拆分为月度多行记录？

在MS Access里处理这种日期区间拆分的需求，因为它不支持像SQL Server那样的递归CTE，所以我们得用一个小技巧——先建一个数字辅助表，然后通过它来生成每个区间内的所有月份。下面一步步来实现：

第一步：创建数字辅助表 #

我们需要一个包含连续数字的表（比如从1到200，足够覆盖大部分年月区间），用来生成每个区间内的月份数。你可以通过以下查询快速生成这个表（命名为Numbers）：

-- 创建Numbers表并插入连续数字
SELECT DISTINCT RowNumber AS Num INTO Numbers
FROM (
    SELECT TOP 200 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNumber
    FROM MSysObjects
) AS Temp;

注：如果你的区间跨度超过200个月，可以把TOP 200改成更大的数字，比如TOP 300。

第二步：编写主查询拆分记录 #

接下来用主查询关联你的业务表和Numbers表，拆分每个PERIOD区间，计算每个月的平均金额。记得把YourTableName替换成你实际的表名：

SELECT 
    t.ID,
    Format(DateAdd("m", n.Num - 1, DateSerial(Left(t.PERIOD, 4), Mid(t.PERIOD, 5, 2), 1)), "mm-yyyy") AS MONTH,
    Round(t.AMOUNT / (DateDiff("m", DateSerial(Left(t.PERIOD, 4), Mid(t.PERIOD, 5, 2), 1), DateSerial(Left(Right(t.PERIOD, 6), 4), Mid(Right(t.PERIOD, 6), 5, 2), 1)) + 1), 2) AS MONTH_AMOUNT
FROM 
    YourTableName AS t,
    Numbers AS n
WHERE 
    n.Num <= (DateDiff("m", DateSerial(Left(t.PERIOD, 4), Mid(t.PERIOD, 5, 2), 1), DateSerial(Left(Right(t.PERIOD, 6), 4), Mid(Right(t.PERIOD, 6), 5, 2), 1)) + 1)
ORDER BY 
    t.ID, MONTH;

关键部分解释： #

1. 拆分PERIOD为起止日期：

   • 用Left(t.PERIOD,4)提取区间开始年份，Mid(t.PERIOD,5,2)提取开始月份；
   • 用Left(Right(t.PERIOD,6),4)提取区间结束年份，Mid(Right(t.PERIOD,6),5,2)提取结束月份；
   • 再用DateSerial把这些数字转换成标准日期格式，方便后续计算。

2. 计算区间总月份数：
   DateDiff("m", 开始日期, 结束日期) + 1——因为DateDiff计算的是两个日期之间的间隔月份数，比如201812到201902间隔2个月，加1后才是实际的3个月份数。

3. 生成每个月份：
   DateAdd("m", n.Num - 1, 开始日期)——通过Numbers表的数字逐个往后推月份，再用Format转换成你需要的mm-yyyy格式。

4. 平均分配金额：
   总金额除以总月份数，用Round函数保留两位小数，和你期望的结果格式一致。

注意事项 #

• 确保你的PERIOD字段格式严格是YYYYMM-YYYYMM，如果格式有变化，需要调整字符串截取的逻辑；
• 如果Numbers表已经存在，直接用即可，不需要重复创建；
• 运行查询前可以先检查起止日期的转换是否正确，避免因为格式问题导致错误。

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