这个帖子要求总数匹配,用其它语言也写过,也算比较典型的需求了,写成博客以免以后搜不到。
总数匹配没有什么高效的算法,只能靠深度优先的算法进行尝试。
-- 测试数据
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