如何填补Access表中各账户的每日日期间隔并补全金额数据？

没问题，我来帮你搞定这个日期补全的需求！首先先把你的Test表数据整理成更清晰的表格形式：

你的需求核心是：为每个账户生成从它最早记录日期到最晚记录日期之间的所有日期，并且每个日期对应当时生效的金额（也就是最近一次金额变更后的数值）。下面我会针对不同主流数据库给出具体的SQL实现方案：

核心思路 #

1. 先获取每个账户的日期边界（最早和最晚记录日期）
2. 生成该边界内的所有日期序列
3. 为原表的每条记录标记金额的生效区间（从当前记录日期到下一条记录日期的前一天）
4. 把生成的日期序列和金额区间关联，得到最终的每日金额记录

SQL Server 实现方案 #

WITH AccountDateRanges AS (
    -- 第一步：获取每个账户的最早、最晚日期
    SELECT 
        Account,
        MIN(Date) AS StartDate,
        MAX(Date) AS EndDate
    FROM Test
    GROUP BY Account
),
RecursiveDates AS (
    -- 第二步：递归生成每个账户的所有日期
    SELECT 
        Account,
        StartDate AS CurrentDate
    FROM AccountDateRanges
    UNION ALL
    SELECT 
        r.Account,
        DATEADD(DAY, 1, r.CurrentDate) AS CurrentDate
    FROM RecursiveDates r
    JOIN AccountDateRanges adr ON r.Account = adr.Account
    WHERE r.CurrentDate < adr.EndDate
),
AccountAmountIntervals AS (
    -- 第三步：标记每条金额记录的生效区间
    SELECT 
        Account,
        Date AS StartInterval,
        -- 用LEAD获取下一条记录的日期，没有下一条就取账户最晚日期+1
        LEAD(Date, 1, DATEADD(DAY, 1, (SELECT MAX(Date) FROM Test WHERE Account = t.Account))) 
            OVER (PARTITION BY Account ORDER BY Date) AS EndInterval,
        Amount
    FROM Test t
)
-- 第四步：关联日期和金额区间，得到结果
SELECT 
    rd.Account,
    rd.CurrentDate AS Date,
    aai.Amount
FROM RecursiveDates rd
JOIN AccountAmountIntervals aai 
    ON rd.Account = aai.Account
    AND rd.CurrentDate >= aai.StartInterval
    AND rd.CurrentDate < aai.EndInterval
ORDER BY rd.Account, rd.CurrentDate;

PostgreSQL 实现方案 #

PostgreSQL可以用generate_series更简洁地生成日期序列：

WITH AccountDateRanges AS (
    SELECT 
        Account,
        MIN(Date) AS StartDate,
        MAX(Date) AS EndDate
    FROM Test
    GROUP BY Account
),
GenerateDates AS (
    -- 用generate_series直接生成日期范围
    SELECT 
        adr.Account,
        generate_series(adr.StartDate, adr.EndDate, INTERVAL '1 day')::DATE AS CurrentDate
    FROM AccountDateRanges adr
),
AccountAmountIntervals AS (
    SELECT 
        Account,
        Date AS StartInterval,
        LEAD(Date, 1, (SELECT MAX(Date) + INTERVAL '1 day' FROM Test WHERE Account = t.Account)) 
            OVER (PARTITION BY Account ORDER BY Date)::DATE AS EndInterval,
        Amount
    FROM Test t
)
SELECT 
    gd.Account,
    gd.CurrentDate AS Date,
    aai.Amount
FROM GenerateDates gd
JOIN AccountAmountIntervals aai 
    ON gd.Account = aai.Account
    AND gd.CurrentDate >= aai.StartInterval
    AND gd.CurrentDate < aai.EndInterval
ORDER BY gd.Account, gd.CurrentDate;

MySQL 8.0+ 实现方案 #

MySQL 8.0及以上支持CTE和窗口函数，写法类似SQL Server：

WITH AccountDateRanges AS (
    SELECT 
        Account,
        MIN(Date) AS StartDate,
        MAX(Date) AS EndDate
    FROM Test
    GROUP BY Account
),
RecursiveDates AS (
    SELECT 
        Account,
        StartDate AS CurrentDate
    FROM AccountDateRanges
    UNION ALL
    SELECT 
        r.Account,
        DATE_ADD(r.CurrentDate, INTERVAL 1 DAY) AS CurrentDate
    FROM RecursiveDates r
    JOIN AccountDateRanges adr ON r.Account = adr.Account
    WHERE r.CurrentDate < adr.EndDate
),
AccountAmountIntervals AS (
    SELECT 
        Account,
        Date AS StartInterval,
        LEAD(Date, 1, DATE_ADD((SELECT MAX(Date) FROM Test WHERE Account = t.Account), INTERVAL 1 DAY)) 
            OVER (PARTITION BY Account ORDER BY Date) AS EndInterval,
        Amount
    FROM Test t
)
SELECT 
    rd.Account,
    rd.CurrentDate AS Date,
    aai.Amount
FROM RecursiveDates rd
JOIN AccountAmountIntervals aai 
    ON rd.Account = aai.Account
    AND rd.CurrentDate >= aai.StartInterval
    AND rd.CurrentDate < aai.EndInterval
ORDER BY rd.Account, rd.CurrentDate;

注意事项 #

• 确保你的Date列是日期类型，如果是字符串格式，需要先转换为日期类型，比如MySQL中用STR_TO_DATE(Date, '%d/%m/%Y')，Oracle中用TO_DATE(Date, 'DD/MM/YYYY')。
• 如果是Oracle数据库，可以用CONNECT BY语法生成日期序列，具体实现可以参考类似的区间关联逻辑。

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