参见英文答案 >
How to get rows having sum equal to given value4个
这是表T： –
这是表T： –
id num ------- 1 50 2 20 3 90 4 40 5 10 6 60 7 30 8 100 9 70 10 80
以下是一个虚构的sql
select * from T where sum(num) = '150'
预期的结果是： –
(一个)
id num ------- 1 50 8 100
(B)
id num ------- 2 20 7 30 8 100
(C)
id num ------- 4 40 5 10 8 100
‘A’案例是最优选的！
我知道这个案子与组合有关.
在现实世界中 – 客户从商店获取商品,并且由于他和商店之间的协议,他每周五付款.付款金额不是商品的确切总数
例如：他得到5本50欧元(= 250欧元)的书,周五他带150欧元,所以前3本书是完美匹配 – 3 * 50 = 150.我需要找到这3本书的id！
任何帮助,将不胜感激！
解决方法
您可以在MSsql中使用递归查询来解决此问题.
第一个递归查询构建具有累积和< = 150的项目树.第二个递归查询采用具有累积和= 150的叶子并将所有这些路径输出到其根.同样在ItemsCount排序的最终结果中,您将首先获得首选组(最小项目数).
WITH CTE as
( SELECT id,num,id as Grp,0 as parent,num as CSum,1 as cnt,CAST(id as Varchar(MAX)) as path
from T where num<=150
UNION all
SELECT t.id,t.num,CTE.Grp as Grp,CTE.id as parent,T.num+CTE.CSum as CSum,CTE.cnt+1 as cnt,CTE.path+','+CAST(t.id as Varchar(MAX)) as path
from T
JOIN CTE on T.num+CTE.CSum<=150
and CTE.id<T.id
),BACK_CTE as
(select CTE.id,CTE.num,CTE.grp,CTE.path,CTE.cnt as cnt,CTE.parent,CSum
from CTE where CTE.CSum=150
union all
select CTE.id,BACK_CTE.path,BACK_CTE.cnt,CTE.CSum
from CTE
JOIN BACK_CTE on CTE.id=BACK_CTE.parent
and CTE.Grp=BACK_CTE.Grp
and BACK_CTE.CSum-BACK_CTE.num=CTE.CSum
)
select id,NUM,path,cnt as ItemsCount from BACK_CTE order by cnt,Id
