SQL Server 进行总数匹配（搜索哪些明细的和等于指定总数）

来源：互联网发布：梦泰网络香港上市编辑：程序博客网时间：2024/05/07 03:50

这个帖子要求总数匹配，用其它语言也写过，也算比较典型的需求了，写成博客以免以后搜不到。

总数匹配没有什么高效的算法，只能靠深度优先的算法进行尝试。

-- 测试数据DECLARE @table1 TABLE(a varchar(10), qty int)INSERT INTO @table1(a,qty)SELECT 'A1',2 UNION ALLSELECT 'A2',3 UNION ALLSELECT 'A3',4 UNION ALLSELECT 'A4',5-- SP参数 --DECLARE @sum intSET @sum = 10-- SP内容 ---- 待选序列，每个成员有选中/不选两种状态，可以看成一棵二叉树DECLARE @t1 TABLE(rn int,a varchar(10), qty int)-- 已选序列，上面这颗二叉树的某条路径上的选中节点，方便回溯和切换路径。DECLARE @t2 TABLE(rn int,a varchar(10), qty int) -- 把qty按降序排列，有机会用更少的记录匹配出结果INSERT INTO @t1SELECT ROW_NUMBER() OVER(ORDER BY qty DESC),       a, qty  FROM @table1 WHERE qty <= @sum -- 先过滤掉不可能的数DECLARE @remain_sum int -- 剩余需要分配的和DECLARE @max_rn intDECLARE @last_rn intDECLARE @qty intSET @remain_sum = @sumSET @max_rn = (SELECT MAX(rn) FROM @t1)SET @last_rn = 0WHILE (@last_rn < @max_rn) AND (@remain_sum > 0)BEGIN    -- 找路径中的下一个可选中节点    INSERT INTO @t2    SELECT TOP 1 *      FROM @t1     WHERE rn > @last_rn -- 方向是向下的       AND qty <= @remain_sum -- 快速剪枝    IF (@@ROWCOUNT <> 0)    BEGIN        -- 有，继续向下        SET @last_rn = (SELECT MAX(rn) FROM @t2)        SELECT @qty = qty FROM @t2 WHERE rn = @last_rn        SET @remain_sum = @remain_sum - @qty    END    ELSE    BEGIN        -- 没有，回溯        SET @last_rn = (SELECT MAX(rn) FROM @t2)        SELECT @qty = qty FROM @t2 WHERE rn = @last_rn        SET @remain_sum = @remain_sum + @qty        DELETE @t2 WHERE rn = @last_rn    END    END-- 结果SELECT *  FROM @t2 WHERE @remain_sum = 0 -- 只有和全部分配了的才是结果。                       -- 比如 @sum = 15 时，序列和才 14，不是结果。

结果数据

         rn a                  qty----------- ---------- -----------          1 A4                   5          3 A2                   3          4 A1                   2

0 0