如何基于动态变化的列连接两张库存功能SQL表？

嘿，针对你手里的入库（ins）和出库（outs）表，想要实现动态列连接的需求，我来拆解两种最贴合库存场景的解决方案，你可以根据实际需求选择：

场景一：按先进先出（FIFO）规则关联出入库记录 #

这应该是库存场景下最常用的动态连接需求——把每一笔出库和对应的入库库存匹配起来，毕竟出库是消耗前面的入库库存嘛。步骤如下：

第一步：给两张表计算累计数量 #

我们需要先算出每一笔入库/出库的累计量，这样就能确定每笔记录对应的库存区间：

计算入库表的累计量

SELECT 
    id,
    direction,
    quantity,
    SUM(quantity) OVER (ORDER BY id) AS cumulative_in,
    -- 算出这笔入库的起始累计位置（第一笔入库从1开始，后续从上一笔累计+1开始）
    COALESCE(SUM(quantity) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) + 1 AS start_in
FROM ins;

执行结果：

计算出库表的累计量

用同样逻辑处理出库表：

SELECT 
    id,
    direction,
    quantity,
    SUM(quantity) OVER (ORDER BY id) AS cumulative_out,
    COALESCE(SUM(quantity) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) + 1 AS start_out
FROM outs;

执行结果：

第二步：基于累计区间动态连接 #

现在我们可以通过区间匹配，把出库需求和对应的入库库存关联起来，还能算出每笔出库消耗了多少对应入库的量：

WITH cumulative_ins AS (
    SELECT 
        id AS in_id,
        quantity AS in_quantity,
        cumulative_in,
        start_in
    FROM (
        SELECT 
            id,
            quantity,
            SUM(quantity) OVER (ORDER BY id) AS cumulative_in,
            COALESCE(SUM(quantity) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) + 1 AS start_in
        FROM ins
    ) AS ins_cum
),
cumulative_outs AS (
    SELECT 
        id AS out_id,
        quantity AS out_quantity,
        cumulative_out,
        start_out
    FROM (
        SELECT 
            id,
            quantity,
            SUM(quantity) OVER (ORDER BY id) AS cumulative_out,
            COALESCE(SUM(quantity) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) + 1 AS start_out
        FROM outs
    ) AS outs_cum
)
SELECT 
    ci.in_id,
    ci.in_quantity,
    co.out_id,
    co.out_quantity,
    -- 计算匹配的数量：取两个区间的交集长度
    LEAST(ci.cumulative_in, co.cumulative_out) - GREATEST(ci.start_in, co.start_out) + 1 AS matched_quantity
FROM cumulative_ins ci
JOIN cumulative_outs co 
    ON ci.cumulative_in >= co.start_out 
    AND ci.start_in <= co.cumulative_out
ORDER BY ci.in_id, co.out_id;

执行后你会得到清晰的出入库对应关系：

场景二：根据动态变化的列名进行连接 #

如果你的需求是动态切换连接的列（比如有时候按id，有时候按其他自定义字段），那可以用动态SQL来实现，以MySQL为例：

-- 这里可以动态设置要用来连接的列名
SET @join_column = 'id'; 

SET @sql = CONCAT(
    'SELECT ins.*, outs.* FROM ins JOIN outs ON ins.', @join_column, ' = outs.', @join_column
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

只要修改@join_column的值，就能切换连接的字段，不过这种场景在库存管理里相对少见，更多还是前面的FIFO关联需求。

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