如何使用纯SQL基于Invoice和EVENT表创建最新前置事件关联新表

不需要使用T-SQL方言特性，也不需要逐行遍历表，用标准SQL的关联查询即可实现需求，执行效率远高于循环遍历方案。

前置字段假设 #

先明确两张业务表的通用字段，你可以根据实际业务字段调整：

• Invoice表：invoice_id（发票唯一标识）、company_id（关联公司ID）、invoice_date（开票日期），可按需补充其他发票字段
• EVENT表：event_id（事件唯一标识）、company_id（关联公司ID）、event_date（事件发生日期），可按需补充其他事件字段

标准SQL实现代码 #

支持窗口函数的数据库方案（适配MySQL 8+/PostgreSQL/Oracle/SQLite 3.25+等所有主流新版数据库） #

该方案逻辑清晰性能最优：

CREATE TABLE `Last Event To Invoice` AS
SELECT 
    t.invoice_id,
    t.company_id,
    t.invoice_date,
    t.event_id,
    t.event_date
    -- 按需添加其他需要的发票、事件字段
FROM (
    SELECT 
        i.*,
        e.*,
        -- 按发票分组，对应事件按发生日期倒序排序
        ROW_NUMBER() OVER (PARTITION BY i.invoice_id ORDER BY e.event_date DESC) AS rank_num
    FROM Invoice i
    LEFT JOIN EVENT e 
        ON i.company_id = e.company_id 
        -- 仅匹配开票日期之前的事件
        AND e.event_date < i.invoice_date
) t
-- 取每个发票对应的最新一条事件
WHERE t.rank_num = 1;

说明：如果仅需要保留有匹配事件的发票记录，将LEFT JOIN替换为INNER JOIN即可；如果存在同公司同日期多事件的场景，可在ORDER BY后补充e.event_id DESC固定取最新生成的事件。

低版本数据库兼容方案（无窗口函数也可使用） #

如果使用不支持窗口函数的旧版数据库，可使用关联子查询实现，同样为标准SQL语法：

CREATE TABLE `Last Event To Invoice` AS
SELECT 
    i.*,
    e.*
FROM Invoice i
LEFT JOIN EVENT e 
    ON i.company_id = e.company_id
    AND e.event_date = (
        SELECT MAX(event_date) 
        FROM EVENT 
        WHERE company_id = i.company_id 
        AND event_date < i.invoice_date
    )
-- 可选：解决同日期多事件的去重问题
AND e.event_id = (
    SELECT MAX(event_id)
    FROM EVENT
    WHERE company_id = i.company_id
    AND event_date < i.invoice_date
)

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