27,580
社区成员
发帖
与我相关
我的任务
分享
declare @t table(a1 decimal(18,2),a2 decimal(18,2),a3 decimal(18,2),a4 decimal(18,2))
insert into @t select 0.2,0.25,0.3,0.33
select top 1 with ties * from --去掉top 1 with ties后可以得到所有的组合情况 此方法为sql2005,如是2000的话,建一个临时表
(
select *,a=a1+a2+a3+a4
from
(select a1*number a1,number as number1 from @t t, master..spt_values m where type='p' and number<=10) t1,
(select a2*number a2, number as number2 from @t t, master..spt_values m where type='p' and number<=10) t2,
(select a3*number a3, number as number3 from @t t, master..spt_values m where type='p' and number<=10) t3,
(select a4*number a4, number as number4 from @t t, master..spt_values m where type='p' and number<=10) t4
) m
where a<=2.0 and number3<>0 and number4<>0 order by abs(a-2)
/*
(1 行受影响)
a1 number1 a2 number2 a3 number3 a4 number4 a
--------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- ---------------------------------------
0.20 1 0.50 2 0.30 1 0.99 3 1.99
0.40 2 0.00 0 0.60 2 0.99 3 1.99
(2 行受影响)
declare @t table(a1 decimal(18,2),a2 decimal(18,2),a3 decimal(18,2),a4 decimal(18,2))
insert into @t select 0.2,0.25,0.3,0.33
select top 1 with ties * from
(
select *,a=a1+a2+a3+a4
from
(select a1*number a1,number as number1 from @t t, master..spt_values m where type='p' and number<=10) t1,
(select a2*number a2, number as number2 from @t t, master..spt_values m where type='p' and number<=10) t2,
(select a3*number a3, number as number3 from @t t, master..spt_values m where type='p' and number<=10) t3,
(select a4*number a4, number as number4 from @t t, master..spt_values m where type='p' and number<=10) t4
) m
order by abs(a-2)
--思想 最小的一块板是0.2平方米,所以完全用这块板最多只能用10,所以这里的number<=10 所得结果a1表示a1用的总量 number1表示用的块数,a 表示总面积
/*
a1 number1 a2 number2 a3 number3 a4 number4 a
--------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- ----------- ---------------------------------------
0.00 0 2.00 8 0.00 0 0.00 0 2.00
0.00 0 0.50 2 1.50 5 0.00 0 2.00
0.20 1 1.50 6 0.30 1 0.00 0 2.00
0.20 1 0.00 0 1.80 6 0.00 0 2.00
0.40 2 1.00 4 0.60 2 0.00 0 2.00
0.60 3 0.50 2 0.90 3 0.00 0 2.00
0.80 4 0.00 0 1.20 4 0.00 0 2.00
1.00 5 1.00 4 0.00 0 0.00 0 2.00
1.20 6 0.50 2 0.30 1 0.00 0 2.00
1.40 7 0.00 0 0.60 2 0.00 0 2.00
2.00 10 0.00 0 0.00 0 0.00 0 2.00
(11 行受影响)
*/