使用SQL条件递归查询为多企业分配物料数量

准备数据：建表与插入示例数据 #

先给出t1（企业物料过剩表）和t2（企业物料需求表）的建表及数据插入语句：

-- 建表：企业物料过剩表t1
CREATE TABLE t1 (
    company_id VARCHAR(20),
    material_id VARCHAR(20),
    surplus_qty INT
);

-- 插入过剩数据
INSERT INTO t1 VALUES
('A', 'M001', 100),
('B', 'M001', 80),
('C', 'M001', 50),
('A', 'M002', 150);

-- 建表：企业物料需求表t2
CREATE TABLE t2 (
    company_id VARCHAR(20),
    material_id VARCHAR(20),
    demand_qty INT
);

-- 插入需求数据
INSERT INTO t2 VALUES
('X', 'M001', 90),
('Y', 'M001', 70),
('Z', 'M001', 60),
('Y', 'M002', 120);

递归查询实现逻辑 #

核心思路是通过递归CTE，每次匹配同一物料下过剩量最高的企业和需求量最高的企业，计算本次可调配数量，更新剩余过剩/需求，直到没有可匹配的记录为止。

WITH RECURSIVE match_process AS (
    -- 初始步骤：获取各物料的初始过剩、需求排名及剩余可调配量
    SELECT
        t1.material_id,
        t1.company_id AS out_company,
        t1.surplus_qty AS out_surplus,
        t1.surplus_qty AS remaining_surplus,
        t2.company_id AS in_company,
        t2.demand_qty AS in_demand,
        t2.demand_qty AS remaining_demand,
        -- 按过剩量降序排名，过剩最多的排第一
        ROW_NUMBER() OVER (PARTITION BY t1.material_id ORDER BY t1.surplus_qty DESC) AS out_rank,
        -- 按需求量降序排名，需求最多的排第一
        ROW_NUMBER() OVER (PARTITION BY t2.material_id ORDER BY t2.demand_qty DESC) AS in_rank
    FROM t1
    JOIN t2 ON t1.material_id = t2.material_id

    UNION ALL

    -- 递归步骤：处理剩余过剩与需求，更新排名
    SELECT
        mp.material_id,
        t1.company_id AS out_company,
        t1.surplus_qty AS out_surplus,
        -- 计算剩余过剩量：若上次剩余过剩>剩余需求，则减去需求，否则置0
        CASE WHEN mp.remaining_surplus > mp.remaining_demand THEN mp.remaining_surplus - mp.remaining_demand ELSE 0 END AS remaining_surplus,
        t2.company_id AS in_company,
        t2.demand_qty AS in_demand,
        -- 计算剩余需求量：若上次剩余需求>剩余过剩，则减去过剩，否则置0
        CASE WHEN mp.remaining_demand > mp.remaining_surplus THEN mp.remaining_demand - mp.remaining_surplus ELSE 0 END AS remaining_demand,
        -- 重新计算过剩排名：仅剩余过剩>0的企业参与
        ROW_NUMBER() OVER (
            PARTITION BY mp.material_id
            ORDER BY CASE WHEN mp.remaining_surplus > mp.remaining_demand THEN mp.remaining_surplus - mp.remaining_demand ELSE 0 END DESC
        ) AS out_rank,
        -- 重新计算需求排名：仅剩余需求>0的企业参与
        ROW_NUMBER() OVER (
            PARTITION BY mp.material_id
            ORDER BY CASE WHEN mp.remaining_demand > mp.remaining_surplus THEN mp.remaining_demand - mp.remaining_surplus ELSE 0 END DESC
        ) AS in_rank
    FROM match_process mp
    -- 关联当前物料下剩余过剩最多的企业（最高优先级调出方）
    JOIN t1 ON mp.material_id = t1.material_id 
        AND t1.company_id = (
            SELECT company_id
            FROM match_process
            WHERE material_id = mp.material_id AND remaining_surplus > 0
            ORDER BY out_rank LIMIT 1
        )
    -- 关联当前物料下剩余需求最多的企业（最高优先级调入方）
    JOIN t2 ON mp.material_id = t2.material_id 
        AND t2.company_id = (
            SELECT company_id
            FROM match_process
            WHERE material_id = mp.material_id AND remaining_demand > 0
            ORDER BY in_rank LIMIT 1
        )
    -- 终止条件：剩余过剩或需求为0时停止递归
    WHERE mp.remaining_surplus > 0 AND mp.remaining_demand > 0
),
-- 提取有效调配记录：过滤中间状态，保留实际调配数据
final_matches AS (
    SELECT
        material_id,
        out_company,
        in_company,
        -- 本次实际调配量：取剩余过剩与需求的较小值
        LEAST(remaining_surplus, remaining_demand) AS allocated_qty
    FROM match_process
    WHERE remaining_surplus > 0 AND remaining_demand > 0
    -- 去重：同一调配组合仅保留一次
    GROUP BY material_id, out_company, in_company
)
-- 最终输出结果
SELECT * FROM final_matches ORDER BY material_id, allocated_qty DESC;

预期输出示例 #

基于测试数据，预期输出如下：

逻辑说明 #

1. 初始CTE：关联所有过剩与需求企业，按优先级排名，初始化剩余可调配量。
2. 递归CTE：每次取当前物料下最高优先级的调出/调入方，计算调配量并更新剩余数据，直到无可用调配量。
3. 结果提取：过滤递归过程中的无效中间记录，去重后输出实际调配结果。

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