关于SQL SERVER 的物料计算的问题,是真的不知道该怎么写语句了......

[匚匚..]

请教一个问题.关于SQLSERVER的物料优化.
不知道SQL能否做到这一点.或者有没有其他更简洁的快速的办法.

先谢谢大家.
问题如图:
附图片:


table a 的行数是不确定的.length是订单使用了的长度.但该物品原料的尺寸是3000MM,怎么样的到右表?注意,这并不是简单的SUM求和然后除以3000MM就能得出的.因为部分尺寸不足3000,也就是LENGTH的和值不大于3000

[匚匚..]

引用 1 楼 锟斤拷锟斤拷 的回复:

https://bbs.csdn.net/topics/398539624 感觉可以参考一下#5的做法

#5的做法是都配对了.但是没有返回唯一值,他的那个是多个组合,没有考虑尺寸的不可重复性.

[锟斤拷锟斤拷]

https://bbs.csdn.net/topics/398539624 感觉可以参考一下#5的做法

[RINK_1]

想不出啥好办法，试试下面的，估计会挺慢的

 WITH CTE_1
 AS
 (SELECT *,ROW_NUMBER() OVER (ORDER BY LENGTH) AS ID FROM #T),
 
 CTE_2
 AS
 (SELECT *,CAST(ID AS VARCHAR) AS ID_GROUP,LENGTH AS SUBTOTAL FROM CTE_1
  UNION ALL
  SELECT A.*,CAST(B.ID_GROUP+','+CAST(A.ID AS VARCHAR) AS VARCHAR),A.LENGTH+B.SUBTOTAL 
  FROM CTE_1 AS A
  JOIN CTE_2 AS B ON A.ID>B.ID
  WHERE A.LENGTH+B.SUBTOTAL<=3000)
 
SELECT * INTO #A FROM CTE_2
 
SELECT *,ROW_NUMBER() OVER (ORDER BY LENGTH) AS ID INTO #B FROM #T

SELECT * INTO #C FROM #A WHERE 1=0

WHILE EXISTS (SELECT 1 FROM #A)

BEGIN 

INSERT INTO #C
SELECT TOP 1 A.* 
FROM #A AS A
JOIN #B AS B ON ','+A.ID_GROUP+',' LIKE '%,'+CAST(B.ID AS VARCHAR)+',%'
ORDER BY A.SUBTOTAL DESC

DELETE #A
FROM #A AS A
JOIN #B AS B ON ','+A.ID_GROUP+',' LIKE '%,'+CAST(B.ID AS VARCHAR)+',%'
WHERE EXISTS (SELECT 1 FROM #C WHERE ','+ID_GROUP+',' LIKE '%,'+CAST(B.ID AS VARCHAR)+',%')

END

SELECT * FROM #C

DROP TABLE #A
DROP TABLE #B
DROP TABLE #C

[锟斤拷锟斤拷]

CREATE TABLE #A(
[LENGTH] INT
)
INSERT INTO #A VALUES (860),(400),(1510),(2300),(870),(1080),(700)
;
WITH ct1 AS
(SELECT *,CONVERT(VARCHAR(MAX),ROW_NUMBER() OVER (ORDER BY LENGTH DESC)) AS ID FROM #A),
ct AS
(SELECT * ,[LENGTH] AS SUMLEN,CONVERT(VARCHAR(max),ID) AS RESULT FROM ct1 WHERE [LENGTH]<=3000 
UNION ALL 
SELECT b.*,B.LENGTH+a.SUMLEN,A.RESULT+','+b.id  FROM ct a INNER JOIN CT1 b ON b.id >= a.id AND a.SUMLEN+b.LENGTH<=3000  
AND CHARINDEX(b.id,RESULT)=0
),
ct2 AS
(SELECT RESULT,SUMLEN,ROW_NUMBER() OVER(ORDER BY SUMLEN DESC) ID1 FROM ct),
CT3 AS
(SELECT TOP 1 *,A.RESULT RES FROM CT2 A ORDER BY A.SUMLEN DESC
UNION ALL
SELECT A.*,
CASE WHEN EXISTS(SELECT 1 FROM XXSPLIT(A.RESULT,',') AA INNER JOIN XXSPLIT(B.RES,',') BB ON AA.VALUE = BB.VALUE) 
THEN B.RES
ELSE A.RESULT+','+B.RES
END 
FROM CT2 A,CT3 B WHERE A.ID1=B.ID1+1
)
SELECT XX.RESULT,SUMLEN FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY RES ORDER BY SUMLEN DESC) ID2 FROM CT3) XX WHERE XX.ID2 = 1

DROP TABLE #A

参考了RINK_1和雨夹雪两位大佬的代码 那个xxsplit()就是string_split()，我的版本没有那个函数

create function xxsplit(@sql varchar(MAX),@xx varchar(20))
returns @temp table(value varchar(20))
as
begin
declare @i int
set @i=charindex(@xx,@sql)
while @i>=1
begin
insert @temp values(left(@sql,@i-1))
set @sql=substring(@sql,@i+1,len(@sql)-@i)
set @i=charindex(@xx,@sql)
end
insert into @temp values(@sql)
return
end

[锟斤拷锟斤拷]

引用 1 楼 锟斤拷锟斤拷 的回复:

https://bbs.csdn.net/topics/398539624 感觉可以参考一下#5的做法

思路就是参考雨夹雪大佬的算法，先求出全部解，再根据sumlen降序排序 然后查出解的ID都是第一次出现的组合，比如第一行的解里出现了1和6，就过滤掉后面含有1或者6的解，反正最后结果看着挺像那么回事的

[shoppo0505]

这个是满足性问题，数据库做不了，有特定的、java类库做这个