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

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

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

-- 测试数据
DECLARE @table1 TABLE(a varchar(10), qty int)

INSERT INTO @table1(a,qty)
SELECT 'A1',2 UNION ALL
SELECT 'A2',3 UNION ALL
SELECT 'A3',4 UNION ALL
SELECT 'A4',5

-- SP参数 --
DECLARE @sum int
SET @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 @t1
SELECT ROW_NUMBER() OVER(ORDER BY qty DESC),
       a, qty
  FROM @table1
 WHERE qty <= @sum -- 先过滤掉不可能的数

DECLARE @remain_sum int -- 剩余需要分配的和
DECLARE @max_rn int
DECLARE @last_rn int
DECLARE @qty int

SET @remain_sum = @sum
SET @max_rn = (SELECT MAX(rn) FROM @t1)
SET @last_rn = 0

WHILE (@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