情境描述：根据表A里的物料去比对表B,然后表A根据A1括号里两个尺寸浪费最少来将A1替换成最省的物料。
表A用量需求表：Table A
A0(自增长ID) A1(物料编号)
-------------------------------
0 ls001-(900*110)
1 ls002-(200*300)
....
表B库存物料表: B1没有重复，可以当作ID来使用 Table B:
B1(库存物料) B2(规格1) B3（规格2）
----------------------------------------------
ls001-(700*200) 700 200
ls001-(910*140) 910 140
ls001-(920*120) 920 120
... ... ...
ls002-(100*200) 100 200
ls002-(200*350) 200 350
ls002-(220*320) 220 320
...
原理是：ls001取(920*120)的话浪费分别是左边920-900=20，右边120-110=10，总共浪费是30， 是ls001库存规格(700*200)，(910*140)，(920*120)里浪费最少的，ls002同理。
最后A1字段被替换后的效果如下：
A0(自增长ID) A1(物料编号)
-------------------------------
0 ls001-(920*120)
1 ls002-(220*320)
...
各位有什么好的方案或者算法可分享来学习借鉴一下 ^_^
算法 SQL
------解决方案--------------------
WITH ta (a0,a1) AS
(
SELECT 0,'ls001-(900*110)' UNION ALL
SELECT 1,'ls002-(200*300)'
)
,tb (B1,B2,B3) AS
(
SELECT 'ls001-(700*200)',700,200 UNION ALL
SELECT 'ls001-(910*140)',910,140 UNION ALL
SELECT 'ls001-(920*120)',920,120 UNION ALL
SELECT 'ls002-(100*200)',100,200 UNION ALL
SELECT 'ls002-(200*350)',200,350 UNION ALL
SELECT 'ls002-(220*320)',220,320
)
,a AS
(
SELECT a0,
SUBSTRING(a1,1,CHARINDEX('-',a1)-1) a9,
SUBSTRING(a1,CHARINDEX('-',a1)+2,CHARINDEX('*',a1)-CHARINDEX('-',a1)-2) a2,
SUBSTRING(a1,CHARINDEX('*',a1)+1,LEN(a1)-CHARINDEX('*',a1)-1) a3
FROM ta
)
,b AS
(
SELECT SUBSTRING(b1,1,CHARINDEX('-',b1)-1) b9,*
FROM tb
)
SELECT a0,a9+'-('+RTRIM(d1.b2)+'*'+RTRIM(d1.b3)+')'
FROM a
CROSS APPLY
(
SELECT TOP 1 b.b2,b.b3--(b.b2-a.a2)+(b.b3-a.a3) a4
FROM b
WHERE b9=a.a9 AND (b.b2-a.a2)>0 AND (b.b3-a.a3)>0
ORDER BY (b.b2-a.a2)+(b.b3-a.a3)
) d1
------解决方案--------------------
ls001取(920*120)的话浪费分别是左边920-900=20，右边120-110=10，总共浪费是30
这个从表上是什么理解的？
------解决方案--------------------
WITH ta (a0,a1,a2,a3) AS
(
SELECT 0,'ls001-(900*110)',900,110 UNION ALL
SELECT 1,'ls002-(200*300)',200,300
)
,tb (B1,B2,B3) AS
(
SELECT 'ls001-(700*200)',700,200 UNION ALL
SELECT 'ls001-(910*140)',910,140 UNION ALL
SELECT 'ls001-(920*120)',920,120 UNION ALL
SELECT 'ls002-(100*200)',100,200 UNION ALL
SELECT 'ls002-(200*350)',200,350 UNION ALL
SELECT 'ls002-(220*320)',220,320
)
,a AS
(
SELECT SUBSTRING(a1,1,CHARINDEX('-',a1)-1) a9,*
FROM ta
)
,b AS
(
SELECT SUBSTRING(b1,1,CHARINDEX('-',b1)-1) b9,*
FROM tb
)
SELECT a0,a9+'-('+RTRIM(d1.b2)+'*'+RTRIM(d1.b3)+')'
FROM a
CROSS APPLY
(
SELECT TOP 1 b.b2,b.b3--(b.b2-a.a2)+(b.b3-a.a3) a4
FROM b
WHERE b9=a.a9 AND (b.b2-a.a2)>0 AND (b.b3-a.a3)>0