日期:2014-05-18 浏览次数:20528 次
-- 返回所有非55.6的任意3个组合中,最小的. select min(t1.d+t2.d+t3.d) from tab t1 cross join tab t2 cross join tab t3 where t1.d<>55.6 and t2.d<>55.6 and t3.d<>55.6
------解决方案--------------------
好像可以通过递归找出全部组合,再找出最佳组合.
------解决方案--------------------
create table tb(w decimal(8,2)) insert into tb select 53.00 insert into tb select 55.60 insert into tb select 62.50 insert into tb select 64.70 insert into tb select 69.50 insert into tb select 72.00 insert into tb select 73.80 insert into tb select 82.30 insert into tb select 91.90 insert into tb select 94.30 insert into tb select 114.70 insert into tb select 114.90 insert into tb select 120.00 insert into tb select 156.30 go ;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=1 union all select 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=2 union all select 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=3 union all select 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=4 union all select 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=5 union all select 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=6 union all select 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=7 union all select 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=8 union all select 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=9 union all select 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=10 union all select 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=11 union all select 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=12 union all select 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=13 union all select 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=14 union all select 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 x from c1,c2,c3,c4,c5,c6,c7,c8,c9