如何基于关联表日期列按月为目标表新增对应额外数据行

核心逻辑是合并Table B原始数据，加上Table A中存在、但Table B同ID无对应自然月记录的补全行，补全行的金额取同ID下早于补入日期的最近一笔发票金额，和你给出的样例规则一致。

具体实现步骤 #

• 保留Table B所有原始记录作为结果基础
• 为Table A每条记录关联同ID下、日期早于当前A记录创建时间的最近一笔发票金额
• 过滤掉A表中已经在B表存在同ID同自然月发票的记录，避免重复
• 合并原始记录和补全记录，按日期排序输出

可直接运行的SQL示例（适配MySQL 8.0+） #

WITH base_b AS (
    SELECT id, invoicedate, amount FROM Table_B
),
a_mapped AS (
    SELECT
        a.id,
        a.createddate AS invoicedate,
        LAST_VALUE(b.amount) OVER (
            PARTITION BY a.id
            ORDER BY STR_TO_DATE(b.invoicedate, '%d/%m/%Y')
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS amount,
        DATE_FORMAT(STR_TO_DATE(a.createddate, '%d/%m/%Y'), '%Y-%m') AS a_month
    FROM Table_A a
    LEFT JOIN Table_B b
        ON a.id = b.id
        AND STR_TO_DATE(b.invoicedate, '%d/%m/%Y') < STR_TO_DATE(a.createddate, '%d/%m/%Y')
),
append_rows AS (
    SELECT DISTINCT id, invoicedate, amount
    FROM a_mapped t
    WHERE NOT EXISTS (
        SELECT 1 FROM base_b b
        WHERE b.id = t.id
        AND DATE_FORMAT(STR_TO_DATE(b.invoicedate, '%d/%m/%Y'), '%Y-%m') = t.a_month
    )
)
SELECT id, invoicedate, amount FROM base_b
UNION ALL
SELECT id, invoicedate, amount FROM append_rows
ORDER BY STR_TO_DATE(invoicedate, '%d/%m/%Y');

适配说明 #

• 如果你用的是其他数据库，只需要替换日期处理函数即可：PostgreSQL用TO_DATE(字段, 'DD/MM/YYYY')做日期转换、TO_CHAR(日期字段, 'YYYY-MM')做月份格式化；SQL Server用CONVERT(DATE, 字段, 103)做日期转换、FORMAT(日期字段, 'yyyy-MM')做月份格式化
• 如果同一个ID在A表有多条同月份的创建记录，SQL里的DISTINCT会自动去重，不会生成重复行
• 运行上述代码针对你给出的样例数据，会完全返回你期望的结果集

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