带后缀的发票数据导出方案优化技术问询

1. 一次性导出所有数据：用窗口函数自动生成后缀 #

如果是一次性导出全部历史数据，不需要维护导出状态，直接用窗口函数就能自动生成带后缀的发票号，完全不需要额外的跟踪表。

以支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server等）为例，SQL语句如下：

SELECT
    date,
    CASE
        -- 对每个发票号按日期排序，第1条保留原号，后续的加-1、-2...
        WHEN ROW_NUMBER() OVER (PARTITION BY invoice_number ORDER BY date) > 1
        THEN CONCAT(invoice_number, '-', ROW_NUMBER() OVER (PARTITION BY invoice_number ORDER BY date) - 1)
        ELSE invoice_number
    END AS exported_invoice_number,
    Amount
FROM invoices
ORDER BY date;

逻辑说明：

• 按invoice_number分组，每组内按date排序
• 用ROW_NUMBER()给每组内的记录编号，第1条编号为1，后续依次递增
• 编号大于1的记录，拼接-编号-1作为后缀，比如第2条就是INV001-1，第3条是INV001-2

2. 分批导出：用UPSERT简化跟踪表操作 #

如果需要分批导出（比如定期同步新数据），可以保留跟踪表，但用**UPSERT（插入或更新）**替代原来的"查询→判断→更新"多步骤操作，一步完成计数器的更新。

假设跟踪表export_tracker结构为：

以MySQL为例，处理单条待导出发票时，执行以下语句即可自动更新计数器：

-- 若发票号已存在，计数器+1；不存在则插入初始值0
INSERT INTO export_tracker (invoice_number, counter)
VALUES ('INV001', 0)
ON DUPLICATE KEY UPDATE counter = counter + 1;

执行后根据counter值生成导出号：

• counter=0 → 导出原号INV001
• counter=1 → 导出INV001-1，以此类推

其他数据库的UPSERT语法：

• PostgreSQL：INSERT INTO export_tracker (invoice_number, counter) VALUES ('INV001', 0) ON CONFLICT (invoice_number) DO UPDATE SET counter = export_tracker.counter + 1;
• SQL Server：MERGE export_tracker AS target USING (SELECT 'INV001' AS invoice_number) AS source ON target.invoice_number = source.invoice_number WHEN MATCHED THEN UPDATE SET counter = counter + 1 WHEN NOT MATCHED THEN INSERT (invoice_number, counter) VALUES (source.invoice_number, 0);

3. 分批导出：用导出日志表替代计数器表 #

另一种思路是创建export_log表，记录每次导出的明细：

下次导出时，统计该发票号已导出的次数，直接生成新的后缀：

SELECT
    i.date,
    CASE
        WHEN COUNT(el.original_invoice_number) = 0
        THEN i.invoice_number
        ELSE CONCAT(i.invoice_number, '-', COUNT(el.original_invoice_number))
    END AS exported_invoice_number,
    i.Amount
FROM invoices i
LEFT JOIN export_log el ON i.invoice_number = el.original_invoice_number
-- 过滤出未导出的新数据（假设按日期筛选）
WHERE i.date > (SELECT MAX(export_date) FROM export_log)
GROUP BY i.date, i.invoice_number, i.Amount
ORDER BY i.date;

导出完成后，将结果插入export_log即可。这种方法不仅能生成后缀，还能完整记录导出历史，方便追溯。

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