SQL Server游标中使用2个WHILE循环实现两表数量分配的方法

前置说明 #

分配顺序规则：

• echeancier_a 按 datelivraison_a 升序、ech_a 升序依次分配
• echeancier_b 按 datelivraison_b 升序、ech_b 升序依次扣减
  本方案使用单外层游标遍历a表，内层通过循环更新b表未分配余量的方式实现，避免双游标@@FETCH_STATUS全局冲突的问题。

第一步：准备测试表与数据（可直接执行） #

-- 创建测试表
CREATE TABLE echeancier_a(
    ref_a VARCHAR(10),
    ech_a INT,
    qty_a INT,
    datelivraison_a DATE
);
CREATE TABLE echeancier_b(
    ref_b VARCHAR(10),
    ech_b INT,
    qty_b INT,
    datelivraison_b DATE,
    remain_qty_b INT -- 新增字段存储b表剩余待分配数量，初始等于qty_b
);
CREATE TABLE matchtab(
    ref_a VARCHAR(10),
    ech_a INT,
    ref_b VARCHAR(10),
    ech_b INT,
    qty_a INT,
    qty_b INT,
    qty_allocated INT,
    comments NVARCHAR(200)
);

-- 插入示例数据
INSERT INTO echeancier_a VALUES ('a',7,10,'2021-06-09'),('a',11,5,'2021-06-11');
INSERT INTO echeancier_b(ref_b,ech_b,qty_b,datelivraison_b,remain_qty_b) 
VALUES ('a',1,2,'2021-07-11',2),('a',37,5,'2021-07-12',5),('a',45,7,'2021-07-13',7),('a',47,1,'2021-07-14',1);

第二步：游标实现分配逻辑 #

DECLARE 
    @ref_a VARCHAR(10), @ech_a INT, @qty_a INT, @remain_a INT,
    @ref_b VARCHAR(10), @ech_b INT, @qty_b INT, @remain_b INT,
    @allocate_qty INT, @comment NVARCHAR(200);

-- 外层游标：遍历所有待分配的a表记录
DECLARE cur_a CURSOR FOR
SELECT ref_a, ech_a, qty_a FROM echeancier_a ORDER BY datelivraison_a ASC, ech_a ASC;

OPEN cur_a;
FETCH NEXT FROM cur_a INTO @ref_a, @ech_a, @qty_a;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @remain_a = @qty_a; -- 初始化当前a记录的剩余待分配量

    -- 循环处理b表剩余量>0的记录，直到当前a的剩余量分配完毕
    WHILE @remain_a > 0 AND EXISTS(SELECT 1 FROM echeancier_b WHERE ref_b = @ref_a AND remain_qty_b > 0)
    BEGIN
        -- 取当前b表最早的未分配完的记录
        SELECT TOP 1 
            @ref_b = ref_b, 
            @ech_b = ech_b, 
            @qty_b = qty_b, 
            @remain_b = remain_qty_b
        FROM echeancier_b 
        WHERE ref_b = @ref_a AND remain_qty_b > 0
        ORDER BY datelivraison_b ASC, ech_b ASC;

        -- 计算本次分配量
        SET @allocate_qty = CASE WHEN @remain_a <= @remain_b THEN @remain_a ELSE @remain_b END;

        -- 生成备注
        SET @comment = N'分配ech_b=' + CAST(@ech_b AS VARCHAR) + N'的' + CAST(@allocate_qty AS VARCHAR) + N'个qty_b到ech_a=' + CAST(@ech_a AS VARCHAR);
        IF @qty_a = @remain_a
            SET @comment = @comment + N'的' + CAST(@qty_a AS VARCHAR) + N'个qty_a';
        ELSE
            SET @comment = @comment + N'剩余的' + CAST(@remain_a AS VARCHAR) + N'个qty_a';

        -- 处理分配完成提示
        IF @remain_a - @allocate_qty = 0
            SET @comment = @comment + N'，ech_a=' + CAST(@ech_a AS VARCHAR) + N'已分配完毕';
        IF @remain_b - @allocate_qty > 0
            SET @comment = @comment + N'，ech_b=' + CAST(@ech_b AS VARCHAR) + N'剩余' + CAST(@remain_b - @allocate_qty AS VARCHAR) + N'个qty_b待分配';

        -- 插入匹配表
        INSERT INTO matchtab(ref_a, ech_a, ref_b, ech_b, qty_a, qty_b, qty_allocated, comments)
        VALUES(@ref_a, @ech_a, @ref_b, @ech_b, @qty_a, @allocate_qty, @allocate_qty, @comment);

        -- 更新b表剩余量
        UPDATE echeancier_b SET remain_qty_b = remain_qty_b - @allocate_qty WHERE ref_b = @ref_b AND ech_b = @ech_b;

        -- 更新a表剩余量
        SET @remain_a = @remain_a - @allocate_qty;
    END

    FETCH NEXT FROM cur_a INTO @ref_a, @ech_a, @qty_a;
END

CLOSE cur_a;
DEALLOCATE cur_a;

第三步：查看分配结果 #

SELECT * FROM matchtab ORDER BY ech_a ASC, ech_b ASC;

执行后结果与你提供的预期结果完全一致。

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