PostgreSQL：采购表关联销售表，匹配采购后最早销售日期

核心思路 #

要实现每条采购记录匹配晚于其日期的最早未被匹配的销售记录，同时保留未匹配的采购行，需解决两个关键点：

1. 确保销售日期严格晚于采购日期
2. 每个销售记录仅被匹配一次（避免重复分配库存）

以下是基于窗口函数的可靠解决方案，兼容PostgreSQL 9.4+版本。

步骤1：定义示例表与数据（模拟用户提供的脚本） #

-- 创建采购表table1
CREATE TABLE table1 (
    id INT,
    date_buy DATE,
    qty_buy INT DEFAULT 1 -- 每次采购1单位
);

-- 插入id=7的采购数据（按日期从旧到新）
INSERT INTO table1 (id, date_buy) VALUES
(7, '2023-01-01'),
(7, '2023-01-03'),
(7, '2023-01-05'),
(7, '2023-01-07');

-- 创建销售表table2
CREATE TABLE table2 (
    id INT,
    date_sold DATE,
    qty_sold INT DEFAULT 1 -- 每次销售1单位
);

-- 插入id=7的销售数据（按日期从旧到新）
INSERT INTO table2 (id, date_sold) VALUES
(7, '2023-01-02'),
(7, '2023-01-04'),
(7, '2023-01-08');

步骤2：执行匹配SQL #

WITH ranked_purchases AS (
    -- 给采购记录按日期排序，生成唯一序号
    SELECT 
        id,
        date_buy,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date_buy) AS purchase_seq
    FROM table1
    WHERE id = 7
),
ranked_sales AS (
    -- 给销售记录按日期排序，生成唯一序号
    SELECT 
        id,
        date_sold,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date_sold) AS sale_seq
    FROM table2
    WHERE id = 7
),
possible_matches AS (
    -- 筛选所有符合日期条件的采购-销售组合，并按优先级排序
    SELECT 
        rp.purchase_seq,
        rp.date_buy,
        rs.sale_seq,
        rs.date_sold,
        -- 对每个采购，标记符合条件的销售的优先级（最早的销售排第一）
        ROW_NUMBER() OVER (PARTITION BY rp.purchase_seq ORDER BY rs.date_sold) AS match_priority
    FROM ranked_purchases rp
    LEFT JOIN ranked_sales rs 
        ON rp.id = rs.id
        AND rs.date_sold > rp.date_buy
),
unique_matches AS (
    -- 确保每个销售仅匹配给最早的符合条件的采购
    SELECT 
        purchase_seq,
        date_buy,
        sale_seq,
        date_sold
    FROM (
        SELECT 
            *,
            -- 对每个销售，标记匹配的采购的优先级（最早的采购排第一）
            ROW_NUMBER() OVER (PARTITION BY sale_seq ORDER BY purchase_seq) AS purchase_priority
        FROM possible_matches
        WHERE sale_seq IS NOT NULL
    ) sub
    WHERE purchase_priority = 1
)
-- 最终输出：所有采购记录 + 匹配的销售记录（未匹配则显示NULL）
SELECT 
    rp.date_buy,
    um.date_sold
FROM ranked_purchases rp
LEFT JOIN unique_matches um 
    ON rp.purchase_seq = um.purchase_seq
ORDER BY rp.date_buy;

步骤3：预期输出 #

为什么之前的滚动和方法无效？ #

table1.rolling_sum_qty_buy=table2.rolling_sum_qty_sold的逻辑仅通过累计数量匹配，未考虑日期先后：

• 若存在销售日期早于采购日期的记录，会错误地将其匹配给后续的采购行
• 无法保证销售记录仅被分配给最早的符合日期条件的采购

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