sql server:选择总和匹配值的行 [英] sql server : select rows who's sum matches a value

问题描述

这里是表 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'

预期结果是:-

(A)

id  num
-------
 1  50
 8  100

(乙)

id  num
-------
 2  20
 7  30
 8  100

(C)

id  num
-------
 4  40
 5  10
 8  100

'A' 大小写是最优选的！

the 'A' case is most preferred !

我知道这个案例与组合有关.

i know this case is related to combinations.

在现实世界中 - 客户从商店购买商品，并且由于他与商店之间的协议，他每周五付款.付款金额不是项目的确切总数例如:他得到 5 本书 50 欧元(= 250 欧元)，周五他带来了 150 欧元，所以前 3 本书是完美匹配 - 3 * 50 = 150.我需要找到这 3 本书的 ID！

in real world - client gets items from a shop, and because of an agreement between him and the shop, he pay every Friday. the payment amount is not the exact total of items for example: he gets 5 books of 50 € ( = 250 € ), and on Friday he bring 150 €, so the first 3 books are perfect match - 3 * 50 = 150. i need to find the id's of those 3 books !

任何帮助将不胜感激！

推荐答案

您可以在 MSSQL 中使用递归查询来解决这个问题.

You can use recursive query in MSSQL to solve this.

SQLFiddle 演示

第一个递归查询构建一棵累积总和 <= 150 的项目树.第二个递归查询采用累积总和 = 150 的叶子并将所有这些路径输出到其根.同样在按 ItemsCount 排序的最终结果中，您将首先获得首选组(项目数最少).

The first recursive query build a tree of items with cumulative sum <= 150. Second recursive query takes leafs with cumulative sum = 150 and output all such paths to its roots. Also in the final results ordered by ItemsCount so you will get preferred groups (with minimal items count) first.

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,CTE.num,CTE.grp,
         BACK_CTE.path,BACK_CTE.cnt, 
         CTE.parent,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,path,Id

这篇关于sql server:选择总和匹配值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！