如何优化装箱SQL代码？减少双循环查询实现最少装箱数

我懂你现在的困扰——这段嵌套循环的SQL虽然能正常运行，但每次循环都要反复查询临时表，数据量一大肯定会卡。毕竟SQL天生就擅长集合操作，循环这种行式处理可不是它的强项。下面给你两个优化方向，一个是彻底替换成集合式的高效实现，另一个是给原代码做些小修小补提升性能：

这个方案完全抛弃循环，用递归CTE模拟贪婪算法（和你原代码逻辑一致：先放最大的批次，再往箱子里塞能放下的批次），性能和扩展性都会好很多：

WITH Lots AS (
    -- 先把所有批次按单位数量降序排序，优先处理大批次
    SELECT 
        LOT_NUMBER, 
        UNIT_COUNT,
        ROW_NUMBER() OVER (ORDER BY UNIT_COUNT DESC) AS rn
    FROM (
        VALUES 
            ('0111590',2), ('0111944',18), ('0111978',5), 
            ('0111982',15), ('0111985',15), ('0111995',14), 
            ('0111996',34), ('0111997',29), ('0111998',16), 
            ('0111999',17), ('0112001',26), ('0112002',29), 
            ('0112004',17)
    ) AS t(LOT_NUMBER, UNIT_COUNT)
),
BoxAssignments AS (
    -- 初始化：第一个批次直接放进第一个箱子
    SELECT 
        rn,
        LOT_NUMBER,
        UNIT_COUNT,
        1 AS BOX_ID,
        UNIT_COUNT AS current_box_total
    FROM Lots
    WHERE rn = 1
    UNION ALL
    -- 递归处理后续批次：判断当前批次能否塞进当前箱子，不行就开新箱子
    SELECT 
        l.rn,
        l.LOT_NUMBER,
        l.UNIT_COUNT,
        CASE WHEN ba.current_box_total + l.UNIT_COUNT > 50 THEN ba.BOX_ID + 1 ELSE ba.BOX_ID END,
        CASE WHEN ba.current_box_total + l.UNIT_COUNT > 50 THEN l.UNIT_COUNT ELSE ba.current_box_total + l.UNIT_COUNT END
    FROM Lots l
    JOIN BoxAssignments ba ON l.rn = ba.rn + 1
),
BoxTotals AS (
    -- 计算每个箱子的总单位数，方便生成虚拟批次
    SELECT 
        BOX_ID,
        SUM(UNIT_COUNT) AS total_units
    FROM BoxAssignments
    GROUP BY BOX_ID
)
-- 合并实际批次和虚拟批次，按要求排序
SELECT 
    ba.BOX_ID,
    ba.LOT_NUMBER,
    ba.UNIT_COUNT
FROM BoxAssignments ba
UNION ALL
SELECT 
    bt.BOX_ID,
    'DUMMY UNITS' AS LOT_NUMBER,
    50 - bt.total_units AS UNIT_COUNT
FROM BoxTotals bt
WHERE 50 - bt.total_units > 0
ORDER BY BOX_ID, UNIT_COUNT DESC;

这个方案的优势： #

• 性能更高：基于集合的操作避免了循环中反复查询、更新临时表的开销，数据量越大优势越明显
• 逻辑清晰：完全复刻你原代码的贪婪装箱逻辑，保证箱子数量最少
• 无需临时表：用CTE替代临时表，代码更简洁，也减少了资源占用

如果你暂时不想替换成集合式实现，也可以给原代码做以下优化，减少不必要的查询开销：

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
CREATE TABLE #temp (
 SYSID BIGINT NOT NULL IDENTITY(1,1),
 LOT_NUMBER NVARCHAR(100) NOT NULL,
 UNIT_COUNT BIGINT NOT NULL,
 BOX_ID BIGINT NULL,
 PROCESSED BIT NULL
)
-- 给临时表加索引，加速循环中的查询
CREATE NONCLUSTERED INDEX IX_temp_BoxId_Processed ON #temp(BOX_ID, PROCESSED) INCLUDE(UNIT_COUNT)

INSERT INTO #temp ( LOT_NUMBER, UNIT_COUNT )
VALUES ('0111590',2), ('0111944',18), ('0111978',5), ('0111982',15), ('0111985',15), ('0111995',14), ('0111996',34), ('0111997',29), ('0111998',16), ('0111999',17), ('0112001',26), ('0112002',29), ('0112004',17)

DECLARE @BOX_ID BIGINT
DECLARE @SYSID1 BIGINT
DECLARE @SYSID2 BIGINT
DECLARE @SYSID3 BIGINT
DECLARE @UNIT_COUNT BIGINT
DECLARE @UnboxedCount INT -- 存储未装箱的数量，避免反复查询全表
DECLARE @ProcessedCount INT -- 存储未处理的批次数量

SET NOCOUNT ON
SET @BOX_ID = 1

-- 初始化未装箱数量
SET @UnboxedCount = (SELECT COUNT(*) FROM #temp WHERE BOX_ID IS NULL)

WHILE @UnboxedCount > 0
BEGIN
    -- 选最大的未装箱批次放进当前箱子
    SELECT TOP 1 @SYSID1 = t.SYSID, @UNIT_COUNT = 50 - t.UNIT_COUNT 
    FROM #temp t WHERE BOX_ID IS NULL ORDER BY t.UNIT_COUNT DESC
    
    UPDATE t SET t.BOX_ID = @BOX_ID FROM #temp t WHERE SYSID = @SYSID1
    SET @UnboxedCount -= 1

    -- 初始化未处理批次数量
    SET @ProcessedCount = (SELECT COUNT(*) FROM #temp WHERE BOX_ID IS NULL AND PROCESSED IS NULL)
    
    WHILE @ProcessedCount > 0
    BEGIN
        SELECT TOP 1 @SYSID2 = t.SYSID 
        FROM #temp t WHERE t.BOX_ID IS NULL AND t.PROCESSED IS NULL ORDER BY t.UNIT_COUNT desc
        
        -- 找能塞进当前箱子的最大批次
        SELECT TOP 1 @SYSID3 = t.SYSID, @UNIT_COUNT = @UNIT_COUNT - t.UNIT_COUNT 
        FROM #temp t WHERE t.BOX_ID IS NULL AND t.UNIT_COUNT <= @UNIT_COUNT ORDER BY t.UNIT_COUNT desc
        
        -- 只有找到合适的批次才更新
        IF @SYSID3 IS NOT NULL
        BEGIN
            UPDATE t SET t.BOX_ID = @BOX_ID FROM #temp t WHERE t.SYSID = @SYSID3
            SET @UnboxedCount -= 1
        END

        UPDATE t SET t.PROCESSED = 1 FROM #temp t WHERE SYSID = @SYSID2
        -- 更新未处理批次数量
        SET @ProcessedCount = (SELECT COUNT(*) FROM #temp WHERE BOX_ID IS NULL AND PROCESSED IS NULL)
    END

    -- 添加虚拟批次补足箱子容量
    INSERT INTO #temp ( BOX_ID, LOT_NUMBER, UNIT_COUNT )
    SELECT @BOX_ID, 'DUMMY UNITS', @UNIT_COUNT WHERE @UNIT_COUNT > 0
    
    SELECT @BOX_ID = @BOX_ID + 1
    UPDATE t SET t.PROCESSED = NULL FROM #temp t WHERE BOX_ID IS NULL
    -- 更新未装箱数量
    SET @UnboxedCount = (SELECT COUNT(*) FROM #temp WHERE BOX_ID IS NULL)
END

SELECT BOX_ID, LOT_NUMBER, UNIT_COUNT FROM #temp ORDER BY BOX_ID,UNIT_COUNT desc

优化点说明： #

• 添加索引：给临时表的BOX_ID和PROCESSED列加索引，大幅减少循环中查询的时间
• 缓存计数：把COUNT(*)的结果存在变量里，避免每次循环都扫描全表，减少IO开销
• 增加空值判断：避免没有合适批次时执行无效的UPDATE操作

总结 #

如果数据量小，原代码改改还能用，但长远来看，递归CTE的集合式实现才是最优选择，它更符合SQL的设计理念，性能和扩展性都更好。

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