比如一块大液晶屏有600厘米,分成如下型号.
(select l from t1 where [email protected])
[email protected]
例:数据1为
53.00
55.60
62.50
64.70
69.50
72.00
73.80
82.30
91.90
94.30
114.70
114.90
120.00
156.30
由于分屏时会预留6-7厘米方便分屏师傅分屏..即损耗在6-7左右.如果指定55.6这个型号之后,其它数据任意组合要求损耗最小.即损耗刚好为6-7之间.我自己想了一个存储过程始终觉得不理想..我发了几个贴子了...求各位DD..有什么好办法
------解决方案--------------------
这是最优化原理，不适合用数据库来处理。
------解决方案--------------------
以600/最小尺寸为循环次数 算出所有切割情况下的剩余量然后取最小值。
问题是都是正方形吗？基础尺寸个数就这么多吗？可选尺寸个数变了。循环深度也要跟着变。。
------解决方案--------------------
- SQL code
-- 返回所有非55.6的任意3个组合中,最小的.select min(t1.d+t2.d+t3.d) from tab t1cross join tab t2cross join tab t3where t1.d<>55.6 and t2.d<>55.6 and t3.d<>55.6
------解决方案--------------------
好像可以通过递归找出全部组合,再找出最佳组合.
------解决方案--------------------
- SQL code
create table tb(w decimal(8,2))insert into tb select 53.00insert into tb select 55.60insert into tb select 62.50insert into tb select 64.70insert into tb select 69.50insert into tb select 72.00insert into tb select 73.80insert into tb select 82.30insert into tb select 91.90insert into tb select 94.30insert into tb select 114.70insert into tb select 114.90insert into tb select 120.00insert into tb select 156.30go;with c as(select row_number()over(order by w)rn,w,floor(593/w)r from tb),c1 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=1union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c1 where num<r),c2 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=2union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c2 where num<r),c3 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=3union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c3 where num<r),c4 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=4union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c4 where num<r),c5 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=5union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c5 where num<r),c6 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=6union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c6 where num<r),c7 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=7union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c7 where num<r),c8 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=8union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c8 where num<r),c9 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=9union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c9 where num<r),c10 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=10union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c10 where num<r),c11 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=11union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c11 where num<r),c12 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=12union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c12 where num<r),c13 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=13union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c13 where num<r),c14 as(select 0 as num,w,r,convert(decimal(8,2),0) x from c where rn=14union allselect num+1,w,r,convert(decimal(8,2),(num+1)*w) from c14 where num<r)select c1.num,c2.num,c3.num,c4.num,c5.num,c6.num,c7.num,c8.num,c9.num,c10.num,c11.num,c12.num,c13.num,c14.num,c1.x+c2.x+c3.x+c4.x+c5.x+c6.x+c7.x+c8.x+c9.x+c10.x+c11.x+c12.x+c13.x+c14.x xfrom c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14where c1.x+c2.x+c3.x+c4.x+c5.x+c6.x+c7.x+c8.x+c9.x+c10.x+c11.x+c12.x+c13.x+c14.x<=593 and c1.x+c2.x+c3.x+c4.x+c5.x+c6.x+c7.x+c8.x+c9.x+c10.x+c11.x+c12.x+c13.x+c14.x>593-53/*num num num num num num num num num num num num num num x----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------2 0 0 0 0 0 0 0 0 0 0 0 0 3 574.905 0 0 0 0 0 0 0 0 0 0 0 0 2 577.608 0 0 0 0 0 0 0 0 0 0 0 0 1 580.3011 0 0 0 0 0 0 0 0 0 0 0 0 0 583.000 0 0 0 0 0 0 0 0 0 0 0 1 3 588.903 0 0 0 0 0 0 0 0 0 0 0 1 2 591.605 0 0 0 0 0 0 0 0 0 0 0 1 1 541.308 0 0 0 0 0 0 0 0 0 0 0 1 0 544.000 0 0 0 0 0 0 0 0 0 0 0 2 2 552.603 0 0 0 0 0 0 0 0 0 0 0 2 1 555.306 0 0 0 0 0 0 0 0 0 0 0 2 0 558.001 0 0 0 0 0 0 0 0 0 0 0 3 1 569.304 0 0 0 0 0 0 0 0 0 0 0 3 0 572.002 0 0 0 0 0 0 0 0 0 0 0 4 0 586.000 0 0 0 0 0 0 0 0 0 0 1 0 3 583.803 0 0 0 0 0 0 0 0 0 0 1 0 2 586.506 0 0 0 0 0 0 0 0 0 0 1 0 1 589.209 0 0 0 0 0 0 0 0 0 0 1 0 0 591.900 0 0 0 0 0 0 0 0 0 0 1 1 2 547.503 0 0 0 0 0 0 0 0 0 0 1 1 1 550.206 0 0 0 0 0 0 0 0 0 0 1 1 0 552.901 0 0 0 0 0 0 0 0 0 0 1 2 1 564.204 0 0 0 0 0 0 0 0 0 0 1 2 0 566.90......会有大量的结果*/godrop table tb