数据匹配问题算法问题

[helodd]

10行记录
id 数量
001 5
002 8
003 20
004 35
005 41
006 2
007 15
008 10
009 9

如何取出 10 行记得中的任意行，数量合计 为 50 ？方法不限。

[AcHerat]

不建议在sql中来实现回朔算法，可以看看回朔算法实现的原理

[lich2005]

可以把数量先排序，然后 1-25 为一组A， 26-49 为一组B，数量 50的肯定直接取走了。 B组的数据由于都大于25，所以不存在两个数字相加的问题。 于是就A组的数字做循环，去尝试和B组的数字相加看是否50，等于50的就取走相应记录。 存在A组的两个数字相加后，再去和B组数字相加等于50的情况。 A + B = 50 ? A + A + B = 50 ? ...

[小野马1209]

这个我也想知道，每次去药店，社保扣减金额都是系统自动用其他药品凑金额

[疯狂的疯]

if OBJECT_ID('tempdb..#A') is not null
begin
	drop table #A
end
 
select v,ROW_NUMBER()over(order by v) id into #A from(
	select 1 id,5 v union all
	select 2 ,8  union all
	select 3 ,20  union all
	select 4 ,35  union all
	select 5 ,41  union all
	select 6 ,2  union all
	select 7 ,15  union all
	select 8 ,10  union all
	select 9 ,9  
)t;

  --六个数字组合
  with cte as(
  select t1.id t1_id,t1.v t1_v,
  t2.id t2_id,t2.v t2_v,
  t3.id t3_id,t3.v t3_v,
  t4.id t4_id,t4.v t4_v,
  t5.id t5_id,t5.v t5_v,
  t6.id t6_id,t6.v t6_v,
  ROW_NUMBER()over(order by t1.id) Ord
  
  from #A t1
 left join #A t2 on t1.id<t2.id
 left join #A t3 on t2.id<t3.id
 left join #A t4 on t3.id<t4.id
 left join #A t5 on t4.id<t5.id
 left join #A t6 on t5.id<t6.id
 ),c as(
 select *,
		SUM(isnull(cte.t1_v,0) + isnull(cte.t2_v,0)) over(partition by Ord) Sum_2,
		SUM(isnull(cte.t1_v,0) + isnull(cte.t2_v,0) + isnull(cte.t3_v,0)) over(partition by Ord) Sum_3,
		SUM(isnull(cte.t1_v,0) + isnull(cte.t2_v,0) + isnull(cte.t3_v,0) + isnull(cte.t4_v,0)) over(partition by Ord) Sum_4,
		SUM(isnull(cte.t1_v,0) + isnull(cte.t2_v,0) + isnull(cte.t3_v,0) + isnull(cte.t4_v,0) + isnull(cte.t5_v,0)) over(partition by Ord) Sum_5,
		SUM(isnull(cte.t1_v,0) + isnull(cte.t2_v,0) + isnull(cte.t3_v,0) + isnull(cte.t4_v,0) + isnull(cte.t5_v,0) + isnull(cte.t6_v,0)) over(partition by Ord) Sum_6

 from cte
 )

  select distinct c.t1_v,
		case when c.Sum_2<=50 then c.t2_v else 0 end t2_v,
		case when c.Sum_3<=50 then c.t3_v else 0 end t3_v,
		case when c.Sum_4<=50 then c.t4_v else 0 end t4_v,
		case when c.Sum_5<=50 then c.t5_v else 0 end t5_v,
		case when c.Sum_6<=50 then c.t6_v else 0 end t6_v
 from c
 where c.Sum_2=50 or c.Sum_3=50 or c.Sum_4=50 or c.Sum_5=50 or c.Sum_6=50

才疏学浅，随便写写，占个位置 等大神的答案、

[雨夹雪]

--数据库搞这个的确有点郁闷
--建表
CREATE TABLE #T
(
	id VARCHAR(10) ,
	数量 INT
)

--测试数据 
INSERT INTO #T VALUES('001',    5)
INSERT INTO #T VALUES('002',    8)
INSERT INTO #T VALUES('003',    20)
INSERT INTO #T VALUES('004',    35)
INSERT INTO #T VALUES('005',    41)
INSERT INTO #T VALUES('006',     2)
INSERT INTO #T VALUES('007',    15)
INSERT INTO #T VALUES('008',    10)
INSERT INTO #T VALUES('009',    9)
 

--方法一,找出所有解
;
WITH ct
AS
(
	SELECT * ,数量 AS 合计,CONVERT(VARCHAR(max),id) AS 结果 FROM #T WHERE 数量<=50 
	UNION ALL 
	SELECT b.*,B.数量+a.合计,结果+','+b.id  FROM ct a INNER JOIN #T b ON b.id >= a.id AND a.合计+b.数量<=50  AND CHARINDEX(b.id,结果)=0 
 )
 SELECT 结果 FROM ct  WHERE 合计=50
 
 

--方法二,找出第一次满足结果的解 
SELECT *, 数量 AS 合计,CONVERT(VARCHAR(max),id) AS 结果 INTO #rst FROM #T WHERE 数量<=50 
WHILE NOT EXISTS(SELECT 1 FROM #rst WHERE 合计=50)
BEGIN 
	INSERT INTO #rst(id,数量,合计,结果)
	SELECT b.id,b.数量,B.数量+a.合计,结果+','+b.id
	FROM #rst a INNER JOIN #T b ON b.id >= a.id AND  a.合计+b.数量<=50  AND CHARINDEX(b.id,结果)=0 
END 

SELECT 结果 FROM #rst WHERE 合计=50
DROP TABLE #rst 

[匚匚..]

引用 5 楼 雨夹雪 的回复:

--数据库搞这个的确有点郁闷
--建表
CREATE TABLE #T
(
	id VARCHAR(10) ,
	数量 INT
)

--测试数据 
INSERT INTO #T VALUES('001',    5)
INSERT INTO #T VALUES('002',    8)
INSERT INTO #T VALUES('003',    20)
INSERT INTO #T VALUES('004',    35)
INSERT INTO #T VALUES('005',    41)
INSERT INTO #T VALUES('006',     2)
INSERT INTO #T VALUES('007',    15)
INSERT INTO #T VALUES('008',    10)
INSERT INTO #T VALUES('009',    9)
 

--方法一,找出所有解
;
WITH ct
AS
(
	SELECT * ,数量 AS 合计,CONVERT(VARCHAR(max),id) AS 结果 FROM #T WHERE 数量<=50 
	UNION ALL 
	SELECT b.*,B.数量+a.合计,结果+','+b.id  FROM ct a INNER JOIN #T b ON b.id >= a.id AND a.合计+b.数量<=50  AND CHARINDEX(b.id,结果)=0 
 )
 SELECT 结果 FROM ct  WHERE 合计=50
 
 

--方法二,找出第一次满足结果的解 
SELECT *, 数量 AS 合计,CONVERT(VARCHAR(max),id) AS 结果 INTO #rst FROM #T WHERE 数量<=50 
WHILE NOT EXISTS(SELECT 1 FROM #rst WHERE 合计=50)
BEGIN 
	INSERT INTO #rst(id,数量,合计,结果)
	SELECT b.id,b.数量,B.数量+a.合计,结果+','+b.id
	FROM #rst a INNER JOIN #T b ON b.id >= a.id AND  a.合计+b.数量<=50  AND CHARINDEX(b.id,结果)=0 
END 

SELECT 结果 FROM #rst WHERE 合计=50
DROP TABLE #rst 

大佬,这个返回的值是多个啊?如果数据只能有一条的组合怎么操作?

[雨夹雪]

top 1 咯