我在举几个例子: A 34 B 23 C 2 D 3 E 43 F 34 结果满足条件的是拿出的是B+D, 28kg,所以不能简单的按照重量排序剔除。
更正 我在举几个例子: A 34 B 23 C 2 D 3 E 43 F 34 结果满足条件的是拿出的是B+C+D, 28kg,所以不能简单的按照重量排序剔除。
SELECT * FROM Product WHERE ID NOT IN( SELECT ID FROM Product A WHERE ( SELECT SUM(Weight) FROM Product WHERE Weight < A.Weight OR Weight = A.Weight AND ID >= A.Weight ) <= (SELECT SUM(Weight) * .8 FROM Product) )
SELECT * FROM Product WHERE ID NOT IN( SELECT ID FROM Product A WHERE ( SELECT SUM(Weight) FROM Product WHERE Weight < A.Weight OR Weight = A.Weight AND ID >= A.ID ) <= (SELECT SUM(Weight) * .8 FROM Product) )
老大,不对。按照你提供的sql语句返回的是e,43kg,明显不如b+c+d重量接近27.8declare @a table(id varchar(10),weight int) insert into @a values ('A',34) insert into @a values ('b',23) insert into @a values ('c',2) insert into @a values ('d',3) insert into @a values ('e',43) insert into @a values ('f',34) SELECT * FROM @a WHERE ID NOT IN( SELECT ID FROM @a A WHERE ( SELECT SUM(Weight) FROM @a WHERE Weight < A.Weight OR Weight = A.Weight AND ID >= A.ID ) <= (SELECT SUM(Weight) * .8 FROM @a) )
抛砖引玉第一题先不考虑效率,全搜索,希望大家吧效率改高些declare @Product table ( ID varchar(10), Weight numeric(10,2) )insert @Product select 'A', 15.11 union all select 'B', 14.22 union all select 'C', 5.33 union all select 'D', 20.44 union all select 'E', 6.55 union all select 'F', 13.66 union all select 'G', 7.77 union all select 'H', 20.88 declare @Weight numeric(10,2)select @Weight=sum(weight)*0.2 from @Product print @Weightdeclare @t table ( IDs varchar(1000), Weight numeric(10,2) )insert @t select ','+id,weight from @Product where Weight<=@Weightwhile not exists (select 1 from @t where weight=@Weight) and exists ( select 1 from @t t,@Product p where t.ids not like '%,'+p.id+'%' and t.weight+p.weight<=@weight and not exists (select 1 from @t where ids=t.ids+','+p.id) ) begin insert @t select t.ids+','+p.id,t.weight+p.weight from @t t,@Product p where t.ids not like '%,'+p.id+'%' and t.weight+p.weight<=@weight and not exists (select 1 from @t where ids=t.ids+','+p.id) endselect top 1 * from @t order by weight desc
-- 测试数据 CREATE TABLE Product(ID char(1), Weight int) INSERT Product SELECT 'A', 34 UNION ALL SELECT 'B', 23 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 3 UNION ALL SELECT 'E', 43 UNION ALL SELECT 'F', 34 GO-- 查询处理 DECLARE @re decimal(18, 2) SELECT @re = SUM(Weight) * .2 FROM Product IF EXISTS(SELECT * FROM Product WHERE Weight = @re) BEGIN SELECT * FROM Product WHERE Weight = @re RETURN END DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), ID char(1), Weight int) INSERT @chk SELECT ',' + A.ID + ',' + B.ID + ',', A.Weight + B.Weight, B.ID, B.Weight FROM Product A, Product B WHERE A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) WHILE @@ROWCOUNT = 0 BEGIN IF EXISTS(SELECT * FROM @chk WHERE Weight = @re) BEGIN SELECT * FROM Product WHERE CHARINDEX(QUOTENAME(ID, ','), (SELECT TOP 1 IDS FROM @chk WHERE Weight = @re)) > 0 RETURN END UPDATE A SET IDS = A.IDS + B.ID + ',', Weights = A.Weights + B.Weight, ID = B.ID, Weight = B.Weight FROM @chk A, Product B WHERE A.Weight < @re AND A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2) SELECT TOP 1 @ids = IDS, @Weights = ABS(Weights - @re) FROM( SELECT IDS = A.IDS + B.ID + ',', Weights = A.Weights + B.Weight, ID = B.ID, Weight = B.Weight FROM @chk A, Product B WHERE A.Weight < @re AND A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) UNION ALL SELECT * FROM @chk )A ORDER BY ABS(Weights - @re)IF EXISTS(SELECT * FROM Product WHERE ABS(Weight - @re) < @Weights) BEGIN SELECT TOP 1 * FROM Product WHERE ABS(Weight - @re) < @Weights END ELSE BEGIN SELECT * FROM Product WHERE CHARINDEX(',' + ID + ',', @ids) > 0 END GODROP TABLE Product -- 结果: ID Weight ---- ----------- B 23 C 2 D 3(3 行受影响)
-- 改一下-- 查询处理 DECLARE @re decimal(18, 2) SELECT @re = SUM(Weight) * .2 FROM Product IF EXISTS(SELECT * FROM Product WHERE Weight = @re) BEGIN SELECT * FROM Product WHERE Weight = @re RETURN END DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), ID char(1), Weight int) INSERT @chk SELECT ',' + A.ID + ',' + B.ID + ',', A.Weight + B.Weight, B.ID, B.Weight FROM Product A, Product B WHERE A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) WHILE @@ROWCOUNT = 0 BEGIN IF EXISTS(SELECT * FROM @chk WHERE Weight = @re) BEGIN SELECT * FROM Product WHERE CHARINDEX(QUOTENAME(ID, ','), (SELECT TOP 1 IDS FROM @chk WHERE Weight = @re)) > 0 RETURN END UPDATE A SET IDS = A.IDS + B.ID + ',', Weights = A.Weights + B.Weight, ID = B.ID, Weight = B.Weight FROM @chk A, Product B WHERE A.Weight < @re AND A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2) SELECT TOP 1 @ids = IDS, @Weights = ABS(Weights - @re) FROM( SELECT IDS = A.IDS + B.ID + ',', Weights = A.Weights + B.Weight, ID = B.ID, Weight = B.Weight FROM @chk A, Product B WHERE A.Weight < @re AND A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) )A ORDER BY Weights - @reIF EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights) BEGIN SELECT TOP 1 * FROM Product WHERE Weight > @re AND Weight - @re < @Weights END ELSE BEGIN SELECT * FROM Product WHERE CHARINDEX(',' + ID + ',', @ids) > 0 END GO
老大,加入把上面的43拆分为27.9,15.1两条记录的话,那么最合适的应该是27.9这条。但是根据sql语句返回的还是b,c,d。所以还是有问题SELECT 'A', 34 UNION ALL SELECT 'B', 23 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 3 UNION ALL SELECT 'E', 27.9 UNION ALL SELECT 'H',15.1 UNION ALL SELECT 'F', 34
declare @Product table ( ID varchar(10), Weight numeric(10,2) )insert @Product select 'A', 15.11 union all select 'B', 14.22 union all select 'C', 5.33 union all select 'D', 20.44 union all select 'E', 6.55 union all select 'F', 13.66 union all select 'G', 7.77 union all select 'H', 20.88 declare @Weight numeric(10,2)select @Weight=sum(weight)*0.2 from @Product print @Weightdeclare @t table ( IDs varchar(1000), Weight numeric(10,2) )insert @t select ','+id,weight from @Product where Weight<=@Weightwhile not exists (select 1 from @t where weight=@Weight) and exists ( select 1 from @t t,@Product p where t.ids not like '%,'+p.id+'%' and t.weight+p.weight<=@weight and not exists (select 1 from @t where ids=t.ids+','+p.id) ) begin insert @t select t.ids+','+p.id,t.weight+p.weight from @t t,@Product p where t.ids not like '%,'+p.id+'%' and t.weight+p.weight<=@weight and not exists (select 1 from @t where ids=t.ids+','+p.id) endif exists (select 1 from @t where weight=@Weight) select * from @t where weight=@Weightselect top 1 t.ids+','+p.id as ids,t.weight+p.weight as weight from @t t,@Product p where t.ids not like '%,'+p.id+'%' and not exists (select 1 from @t where ids=t.ids+','+p.id) order by t.weight+p.weight 结果: IDs Weight --------------------------------- ------------ ,G,F 21.43(所影响的行数为 1 行)
if exists (select 1 from @t where weight=@Weight) select * from @t where weight=@Weightselect top 1 t.ids+','+p.id as ids,t.weight+p.weight as weight from @t t,@Product p where t.ids not like '%,'+p.id+'%' and not exists (select 1 from @t where ids=t.ids+','+p.id) order by t.weight+p.weight -->if exists (select 1 from @t where weight=@Weight) select * from @t where weight=@Weight else select top 1 t.ids+','+p.id as ids,t.weight+p.weight as weight from @t t,@Product p where t.ids not like '%,'+p.id+'%' and not exists (select 1 from @t where ids=t.ids+','+p.id) order by t.weight+p.weight
Haiwer,你把下面一组数带入, SELECT 'A', 34 UNION ALL SELECT 'B', 23 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 3 UNION ALL SELECT 'E', 27.9 UNION ALL SELECT 'H',15.1 UNION ALL SELECT 'F', 34sum*0.2=27.8 ,所以应该选择E,27.9结果按照你的sql语句,b,c,d 为28不是最佳。
declare @Product table ( ID varchar(10), Weight numeric(10,2) )insert @Product SELECT 'A', 34 UNION ALL SELECT 'B', 23 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 3 UNION ALL SELECT 'E', 27.9 UNION ALL SELECT 'F', 34 UNION ALL SELECT 'G', 15.1/* select 'A', 15.11 union all select 'B', 14.22 union all select 'C', 5.33 union all select 'D', 20.44 union all select 'E', 6.55 union all select 'F', 13.66 union all select 'G', 7.77 union all select 'H', 20.88 */declare @Weight numeric(10,2)select @Weight=sum(weight)*0.2 from @Product print @Weightdeclare @t table ( IDs varchar(1000), Weight numeric(10,2) )insert @t select ','+id,weight from @Product where Weight<=@Weightwhile not exists (select 1 from @t where weight=@Weight) and exists ( select 1 from @t t,@Product p where t.ids not like '%,'+p.id+'%' and t.weight+p.weight<=@weight and not exists (select 1 from @t where ids=t.ids+','+p.id) ) begin insert @t select t.ids+','+p.id,t.weight+p.weight from @t t,@Product p where t.ids not like '%,'+p.id+'%' and t.weight+p.weight<=@weight and not exists (select 1 from @t where ids=t.ids+','+p.id) endif exists (select 1 from @t where weight=@Weight) select * from @t where weight=@Weight else select top 1 * from ( select ','+id as ids,weight from @Product where weight>@weight union all select t.ids+','+p.id as ids,t.weight+p.weight as weight from @t t,@Product p where t.ids not like '%,'+p.id+'%' and not exists (select 1 from @t where ids=t.ids+','+p.id) ) as t order by weight,len(ids)
-- 测试数据 CREATE TABLE Product(ID char(1), Weight DECIMAL(18, 2)) INSERT Product SELECT 'A', 34 UNION ALL SELECT 'B', 23 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 3 UNION ALL SELECT 'E', 27.9 UNION ALL SELECT 'H', 15.1 UNION ALL SELECT 'F', 34 GO-- 查询处理 DECLARE @re decimal(18, 2) SELECT @re = SUM(Weight) * .2 FROM Product IF EXISTS(SELECT * FROM Product WHERE Weight = @re) BEGIN SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight = @re RETURN END DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), ID char(1), Weight DECIMAL(18, 2)) INSERT @chk SELECT ',' + A.ID + ',' + B.ID + ',', A.Weight + B.Weight, B.ID, B.Weight FROM Product A, Product B WHERE A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) WHILE @@ROWCOUNT = 0 BEGIN IF EXISTS(SELECT * FROM @chk WHERE Weight = @re) BEGIN SELECT [20%] = @re, * FROM Product WHERE CHARINDEX(QUOTENAME(ID, ','), (SELECT TOP 1 IDS FROM @chk WHERE Weight = @re)) > 0 RETURN END UPDATE A SET IDS = A.IDS + B.ID + ',', Weights = A.Weights + B.Weight, ID = B.ID, Weight = B.Weight FROM @chk A, Product B WHERE A.Weight < @re AND A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2) SELECT TOP 1 @ids = IDS, @Weights = ABS(Weights - @re) FROM( SELECT IDS = A.IDS + B.ID + ',', Weights = A.Weights + B.Weight, ID = B.ID, Weight = B.Weight FROM @chk A, Product B WHERE A.Weight < @re AND A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) )A ORDER BY Weights - @reIF EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights) BEGIN SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight > @re AND Weight - @re < @Weights ORDER BY Weight END ELSE BEGIN SELECT [20%] = @re, * FROM Product WHERE CHARINDEX(',' + ID + ',', @ids) > 0 END GODROP TABLE Product-- 结果 20% ID Weight --------------------------------------- ---- --------------------------------------- 27.80 E 27.90(1 行受影响)
老大,把刚才的27.9拆分成27.3,27.5,0.1三条,正确结果应该是e+f,27.8于20%正好符合。但是sql结果返回的是I,34 SELECT 'A', 34 UNION ALL SELECT 'B', 23 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 3 UNION ALL SELECT 'E', 27.3 UNION ALL SELECT 'F', 0.5 UNION ALL SELECT 'G', 0.1 UNION ALL SELECT 'H', 15.1 UNION ALL SELECT 'I', 34
-- 测试数据 CREATE TABLE Product(ID char(1), Weight DECIMAL(18, 2)) INSERT Product SELECT 'A', 34 UNION ALL SELECT 'B', 23 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 3 UNION ALL SELECT 'E', 27.3 UNION ALL SELECT 'F', 0.5 UNION ALL SELECT 'G', 0.1 UNION ALL SELECT 'H', 15.1 UNION ALL SELECT 'I', 34 GO-- 查询处理 DECLARE @re decimal(18, 2) SELECT @re = SUM(Weight) * .2 FROM Product IF EXISTS(SELECT * FROM Product WHERE Weight = @re) BEGIN SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight = @re RETURN END DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), ID char(1), Weight DECIMAL(18, 2)) INSERT @chk SELECT ',' + A.ID + ',' + B.ID + ',', A.Weight + B.Weight, B.ID, B.Weight FROM Product A, Product B WHERE A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) WHILE @@ROWCOUNT > 0 BEGIN IF EXISTS(SELECT * FROM @chk WHERE Weights = @re) BEGIN SELECT [20%] = @re, * FROM Product WHERE CHARINDEX(',' + ID + ',', (SELECT TOP 1 IDS FROM @chk WHERE Weights = @re)) > 0 RETURN END UPDATE A SET IDS = A.IDS + B.ID + ',', Weights = A.Weights + B.Weight, ID = B.ID, Weight = B.Weight FROM @chk A, Product B WHERE A.Weight < @re AND A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2) SELECT TOP 1 @ids = IDS, @Weights = Weights - @re FROM( SELECT IDS = A.IDS + B.ID + ',', Weights = A.Weights + B.Weight, ID = B.ID, Weight = B.Weight FROM @chk A, Product B WHERE A.Weight < @re AND A.Weight + B.Weight >= @re )A ORDER BY Weights - @reIF EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights) BEGIN SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight > @re AND Weight - @re < @Weights ORDER BY Weight END ELSE BEGIN SELECT [20%] = @re, * FROM Product WHERE CHARINDEX(',' + ID + ',', @ids) > 0 END GODROP TABLE Product-- 结果 20% ID Weight --------------------------------------- ---- ---------- 27.80 E 27.30 27.80 F 0.50(2 行受影响)
SELECT 'A', 34 UNION ALL SELECT 'B', 23 UNION ALL SELECT 'C', 24 UNION ALL SELECT 'D', 3这个怎么没有结果?
-- 测试数据 CREATE TABLE Product(ID char(1), Weight DECIMAL(18, 2)) INSERT Product SELECT 'A', 34 UNION ALL SELECT 'B', 23 UNION ALL SELECT 'C', 24 UNION ALL SELECT 'D', 3 GO-- 查询处理 DECLARE @re decimal(18, 2) SELECT @re = SUM(Weight) * .2 FROM Product IF EXISTS(SELECT * FROM Product WHERE Weight = @re) BEGIN SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight = @re RETURN END DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), ID char(1), Weight DECIMAL(18, 2)) INSERT @chk SELECT ',' + A.ID + ',' + B.ID + ',', A.Weight + B.Weight, B.ID, B.Weight FROM Product A, Product B WHERE A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) WHILE @@ROWCOUNT > 0 BEGIN IF EXISTS(SELECT * FROM @chk WHERE Weights = @re) BEGIN SELECT [20%] = @re, * FROM Product WHERE CHARINDEX(',' + ID + ',', (SELECT TOP 1 IDS FROM @chk WHERE Weights = @re)) > 0 RETURN END UPDATE A SET IDS = A.IDS + B.ID + ',', Weights = A.Weights + B.Weight, ID = B.ID, Weight = B.Weight FROM @chk A, Product B WHERE A.Weight < @re AND A.Weight + B.Weight <= @re AND(A.Weight > B.Weight OR A.Weight = B.Weight AND A.ID < B.ID) ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2) SELECT TOP 1 @ids = IDS, @Weights = Weights - @re FROM( SELECT IDS = A.IDS + B.ID + ',', Weights = A.Weights + B.Weight, ID = B.ID, Weight = B.Weight FROM @chk A, Product B WHERE A.Weight < @re AND A.Weight + B.Weight >= @re )A ORDER BY Weights - @reIF @@ROWCOUNT = 0 OR EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights) BEGIN SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight > @re AND Weight - @re < ISNULL(@Weights, @re) ORDER BY Weight END ELSE BEGIN SELECT [20%] = @re, * FROM Product WHERE CHARINDEX(',' + ID + ',', @ids) > 0 END GODROP TABLE Product-- 结果 20% ID Weight --------------------------------------- ---- --------------------------------------- 16.80 B 23.00(1 行受影响)
再试验:sum*02=17.2但是结果返回33。SELECT 'A', 3 UNION ALL SELECT 'B', 2 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 3 UNION ALL SELECT 'e', 3 UNION ALL SELECT 'f', 3 UNION ALL SELECT 'g', 33 UNION ALL SELECT 'h', 34 UNION ALL SELECT 'i', 3
-- 测试数据 CREATE TABLE Product(ID char(1), Weight DECIMAL(18, 2)) INSERT Product SELECT 'A', 3 UNION ALL SELECT 'B', 2 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 3 UNION ALL SELECT 'e', 3 UNION ALL SELECT 'f', 3 UNION ALL SELECT 'g', 33 UNION ALL SELECT 'h', 34 UNION ALL SELECT 'i', 3 GO-- 查询处理 DECLARE @re decimal(18, 2) SELECT @re = SUM(Weight) * .2 FROM ProductDECLARE @l int SET @l = 0 DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), Level int) INSERT @chk SELECT ',' + A.ID + ',', A.Weight, @l FROM Product A WHERE A.Weight <= @re WHILE @@ROWCOUNT > 0 BEGIN IF EXISTS(SELECT * FROM @chk WHERE Level = @l AND Weights = @re) BEGIN SELECT [20%] = @re, * FROM Product WHERE CHARINDEX(',' + ID + ',', (SELECT TOP 1 IDS FROM @chk WHERE Level = @l AND Weights = @re)) > 0 RETURN END SET @l = @l + 1 INSERT @chk SELECT A.IDS + B.ID + ',', A.Weights + B.Weight, @l FROM @chk A, Product B WHERE A.Level = @l - 1 AND A.Weights + B.Weight <= @re AND CHARINDEX(',' + B.ID + ',', A.IDS) = 0 ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2) SELECT TOP 1 @ids = IDS, @Weights = @re - Weights FROM @chk ORDER BY Weights DESCIF @@ROWCOUNT = 0 OR EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights) BEGIN SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight > @re AND Weight - @re < ISNULL(@Weights, @re) ORDER BY Weight END ELSE BEGIN SELECT [20%] = @re, * FROM Product WHERE CHARINDEX(',' + ID + ',', @ids) > 0 UNION ALL SELECT * FROM( SELECT TOP 1 [20%] = @re, * FROM Product WHERE CHARINDEX(',' + ID + ',', @ids) = 0 AND Weight >= @Weights ORDER BY Weight )A END GODROP TABLE Product
SELECT 'A', 3 UNION ALL SELECT 'B', 42 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 12 UNION ALL SELECT 'e', 3 UNION ALL SELECT 'f', 3 UNION ALL SELECT 'g', 33 UNION ALL SELECT 'h', 34 UNION ALL SELECT 'i', 3 GO 结果是,实际上只要选择g,33就可以了 . 27.00 A 3.00 27.00 C 2.00 27.00 D 12.00 27.00 e 3.00 27.00 f 3.00 27.00 i 3.00 27.00 g 33.00
-- 测试数据 CREATE TABLE Product(ID char(1), Weight DECIMAL(18, 2)) INSERT Product SELECT 'A', 3 UNION ALL SELECT 'B', 42 UNION ALL SELECT 'C', 2 UNION ALL SELECT 'D', 12 UNION ALL SELECT 'e', 3 UNION ALL SELECT 'f', 3 UNION ALL SELECT 'g', 33 UNION ALL SELECT 'h', 34 UNION ALL SELECT 'i', 3 GO-- 查询处理 DECLARE @re decimal(18, 2) SELECT @re = SUM(Weight) * .2 FROM ProductDECLARE @l int SET @l = 0 DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), Level int) INSERT @chk SELECT ',' + A.ID + ',', A.Weight, @l FROM Product A WHERE A.Weight <= @re WHILE @@ROWCOUNT > 0 BEGIN IF EXISTS(SELECT * FROM @chk WHERE Level = @l AND Weights = @re) BEGIN SELECT [20%] = @re, * FROM Product WHERE CHARINDEX(',' + ID + ',', (SELECT TOP 1 IDS FROM @chk WHERE Level = @l AND Weights = @re)) > 0 RETURN END SET @l = @l + 1 INSERT @chk SELECT A.IDS + B.ID + ',', A.Weights + B.Weight, @l FROM @chk A, Product B WHERE A.Level = @l - 1 AND A.Weights + B.Weight <= @re AND CHARINDEX(',' + B.ID + ',', A.IDS) = 0 ENDINSERT @chk SELECT A.IDS + B.ID + ',', A.Weights + B.Weight, @l FROM @chk A, Product B WHERE A.Level = @l - 1 AND A.Weights + B.Weight >= @re AND CHARINDEX(',' + B.ID + ',', A.IDS) = 0DECLARE @ids varchar(8000), @Weights decimal(18, 2) SELECT TOP 1 @ids = IDS, @Weights = Weights - @re FROM @chk WHERE Weights > @re ORDER BY WeightsIF @@ROWCOUNT = 0 OR EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights) BEGIN SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight > @re AND Weight - @re < ISNULL(@Weights, @re) ORDER BY Weight END ELSE BEGIN SELECT [20%] = @re, * FROM Product WHERE CHARINDEX(',' + ID + ',', @ids) > 0 END GODROP TABLE Product
SELECT 'A', 3 UNION ALL SELECT 'B', 42 UNION ALL SELECT 'C', 22 UNION ALL SELECT 'D', 12 UNION ALL SELECT 'e', 32 UNION ALL SELECT 'f', 33 UNION ALL SELECT 'g', 33 UNION ALL SELECT 'h', 34 UNION ALL SELECT 'i', 3结果出来是这个,应该A+b,或b+i 45 42.80 A 3.00 42.80 C 22.00 42.80 D 12.00 42.80 e 32.00 42.80 i 3.00
这两个问题我考虑了很久,可能没有什么好的算法如果一定要用sql帮忙解决的话,估计只能找出可能的排列组合,寻解了。 如Product(Id char(1),Weight decimal(9,2)) 有20条记录的话,总重量1000kg,如果要求满足条件1+2,及sum*0.2=400kg 且记录数最少,按照我以前的思路处理如下:存在一条记录就满足条件的@sum0.2,放到一个临时表中select top 1 * into #temp1 from Product where [email protected] 存在2条记录重量和满足条件@sum0.2 ,插入到临时表中 insert into #temp1 select top 1 a.id+b.id, a.weight+b.wiehgt from product a ,product b where a.id<>b.id and a.weight+b.weight>[email protected] order by a.weight+b.weight desc存在3条记录重量之和满足@sum0.2 insert into #temp1 select top 1 a.id+b.id+c.id, a.weight+b.weihgt+c.weight from product a ,product b ,product c where a.id<>b.id and a.id<>c.id and b.id<>c.id a.weight+b.weight+c.weight>[email protected] order by a.weight+b.weight+c.weight desc...存在n条记录记录满足条件,n是有上限的,即product列表中重量从小到大排序,前面n条记录 之和>[email protected]就可以了。最终结果是select top 1 * from #temp1 order by weight desc ,就是最佳方案了。所以这里只能处理数十条记录,多了的话服务器承受不了了。以上是我的原始求解法,答案是正确的,但是效率非常低下。
这两个问题我考虑了很久,可能没有什么好的算法如果一定要用sql帮忙解决的话,估计只能找出可能的排列组合,寻解了。 如Product(Id char(1),Weight decimal(9,2)) 有20条记录的话,总重量1000kg,如果要求满足条件1+2,及sum*0.2=400kg 且记录数最少,按照我以前的思路处理如下:存在一条记录就满足条件的@sum0.2,放到一个临时表中select top 1 * into #temp1 from Product where weight>[email protected] 存在2条记录重量和满足条件@sum0.2 ,插入到临时表中 insert into #temp1 select top 1 a.id+b.id, a.weight+b.wiehgt from product a ,product b where a.id<>b.id and a.weight+b.weight>[email protected] order by a.weight+b.weight desc存在3条记录重量之和满足@sum0.2 insert into #temp1 select top 1 a.id+b.id+c.id, a.weight+b.weihgt+c.weight from product a ,product b ,product c where a.id<>b.id and a.id<>c.id and b.id<>c.id a.weight+b.weight+c.weight>[email protected] order by a.weight+b.weight+c.weight desc...存在n条记录记录满足条件,n是有上限的,即product列表中重量从小到大排序,前面n条记录 之和>[email protected]就可以了。最终结果是select top 1 * from #temp1 order by weight desc ,就是最佳方案了。所以这里只能处理数十条记录,多了的话服务器承受不了了。以上是我的原始求解法,答案是正确的,但是效率非常低下。
假设所有产品重量都小于<100*0.2的话,select * ...一条记录也没有了。
如:A 10,B12,C 10,D 13..... 总重量是200,结果如何?
不对:
假设所有产品重量都小于<100*0.2的话,select * ...一条记录也没有了。
如:A 10,B12,C 10,D 13..... 总重量是200,结果如何?
-----------------------------------------沒明白,你不是說“尽可能接近20%(但是不能少于20%),”,如果“总重量是200”,是沒有滿足條件的紀錄啊。
A 34
B 23
C 2
D 3
E 43
F 34
结果满足条件的是拿出的是B+D, 28kg,所以不能简单的按照重量排序剔除。
我在举几个例子:
A 34
B 23
C 2
D 3
E 43
F 34
结果满足条件的是拿出的是B+C+D, 28kg,所以不能简单的按照重量排序剔除。
FROM Product
WHERE ID NOT IN(
SELECT ID FROM Product A
WHERE (
SELECT SUM(Weight) FROM Product
WHERE Weight < A.Weight
OR Weight = A.Weight AND ID >= A.Weight
) <= (SELECT SUM(Weight) * .8 FROM Product)
)
1.求平均直64,周期>=64的单独分配机台,直到周期>64的分配完毕 2.剩余的都是<64的,按周期从大到小顺序排列,设为a 组数据,取最大A1(这里是40),
然后再从剩余数据中取〈=24的从大到小排列一组 B组。取B组最大直B1。(这里是24)。如果A1+B1〉=64,将A1,B1分到一机台,否则查找B组除B1外的最大直B2,如果A1+B1+B2<=64 继续。
3.A组中剔除 查找出的一组直,继续以此方法查找分配别的机
FROM Product
WHERE ID NOT IN(
SELECT ID FROM Product A
WHERE (
SELECT SUM(Weight) FROM Product
WHERE Weight < A.Weight
OR Weight = A.Weight AND ID >= A.ID
) <= (SELECT SUM(Weight) * .8 FROM Product)
)
insert into @a values ('A',34)
insert into @a values ('b',23)
insert into @a values ('c',2)
insert into @a values ('d',3)
insert into @a values ('e',43)
insert into @a values ('f',34)
SELECT *
FROM @a
WHERE ID NOT IN(
SELECT ID FROM @a A
WHERE (
SELECT SUM(Weight) FROM @a
WHERE Weight < A.Weight
OR Weight = A.Weight AND ID >= A.ID
) <= (SELECT SUM(Weight) * .8 FROM @a)
)
ID varchar(10),
Weight numeric(10,2)
)insert @Product
select
'A', 15.11
union all select
'B', 14.22
union all select
'C', 5.33
union all select
'D', 20.44
union all select
'E', 6.55
union all select
'F', 13.66
union all select
'G', 7.77
union all select
'H', 20.88
declare @Weight numeric(10,2)select @Weight=sum(weight)*0.2 from @Product
print @Weightdeclare @t table (
IDs varchar(1000),
Weight numeric(10,2)
)insert @t
select ','+id,weight from @Product
where Weight<=@Weightwhile not exists (select 1 from @t where weight=@Weight)
and exists (
select 1 from @t t,@Product p
where t.ids not like '%,'+p.id+'%'
and t.weight+p.weight<=@weight
and not exists (select 1 from @t where ids=t.ids+','+p.id)
)
begin
insert @t
select t.ids+','+p.id,t.weight+p.weight
from @t t,@Product p
where t.ids not like '%,'+p.id+'%'
and t.weight+p.weight<=@weight
and not exists (select 1 from @t where ids=t.ids+','+p.id)
endselect top 1 * from @t order by weight desc
20.79 --20%的数
IDs Weight
--------------------------------- ------------
,E,B 20.77(所影响的行数为 1 行)
也就是说从这组产品种至少要抽查20,792kg,但是你提供的结果,E,B 20.77
不满足条件。
CREATE TABLE Product(ID char(1), Weight int)
INSERT Product
SELECT 'A', 34
UNION ALL SELECT 'B', 23
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 3
UNION ALL SELECT 'E', 43
UNION ALL SELECT 'F', 34
GO-- 查询处理
DECLARE @re decimal(18, 2)
SELECT @re = SUM(Weight) * .2 FROM Product
IF EXISTS(SELECT * FROM Product WHERE Weight = @re)
BEGIN
SELECT * FROM Product WHERE Weight = @re
RETURN
END
DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), ID char(1), Weight int)
INSERT @chk SELECT
',' + A.ID + ',' + B.ID + ',',
A.Weight + B.Weight,
B.ID, B.Weight
FROM Product A, Product B
WHERE A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
WHILE @@ROWCOUNT = 0
BEGIN
IF EXISTS(SELECT * FROM @chk WHERE Weight = @re)
BEGIN
SELECT * FROM Product
WHERE CHARINDEX(QUOTENAME(ID, ','), (SELECT TOP 1 IDS FROM @chk WHERE Weight = @re)) > 0
RETURN
END
UPDATE A SET
IDS = A.IDS + B.ID + ',',
Weights = A.Weights + B.Weight,
ID = B.ID,
Weight = B.Weight
FROM @chk A, Product B
WHERE A.Weight < @re
AND A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2)
SELECT TOP 1 @ids = IDS, @Weights = ABS(Weights - @re)
FROM(
SELECT
IDS = A.IDS + B.ID + ',',
Weights = A.Weights + B.Weight,
ID = B.ID,
Weight = B.Weight
FROM @chk A, Product B
WHERE A.Weight < @re
AND A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
UNION ALL
SELECT * FROM @chk
)A
ORDER BY ABS(Weights - @re)IF EXISTS(SELECT * FROM Product WHERE ABS(Weight - @re) < @Weights)
BEGIN
SELECT TOP 1 *
FROM Product
WHERE ABS(Weight - @re) < @Weights
END
ELSE
BEGIN
SELECT * FROM Product
WHERE CHARINDEX(',' + ID + ',', @ids) > 0
END
GODROP TABLE Product
-- 结果:
ID Weight
---- -----------
B 23
C 2
D 3(3 行受影响)
DECLARE @re decimal(18, 2)
SELECT @re = SUM(Weight) * .2 FROM Product
IF EXISTS(SELECT * FROM Product WHERE Weight = @re)
BEGIN
SELECT * FROM Product WHERE Weight = @re
RETURN
END
DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), ID char(1), Weight int)
INSERT @chk SELECT
',' + A.ID + ',' + B.ID + ',',
A.Weight + B.Weight,
B.ID, B.Weight
FROM Product A, Product B
WHERE A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
WHILE @@ROWCOUNT = 0
BEGIN
IF EXISTS(SELECT * FROM @chk WHERE Weight = @re)
BEGIN
SELECT * FROM Product
WHERE CHARINDEX(QUOTENAME(ID, ','), (SELECT TOP 1 IDS FROM @chk WHERE Weight = @re)) > 0
RETURN
END
UPDATE A SET
IDS = A.IDS + B.ID + ',',
Weights = A.Weights + B.Weight,
ID = B.ID,
Weight = B.Weight
FROM @chk A, Product B
WHERE A.Weight < @re
AND A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2)
SELECT TOP 1 @ids = IDS, @Weights = ABS(Weights - @re)
FROM(
SELECT
IDS = A.IDS + B.ID + ',',
Weights = A.Weights + B.Weight,
ID = B.ID,
Weight = B.Weight
FROM @chk A, Product B
WHERE A.Weight < @re
AND A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
)A
ORDER BY Weights - @reIF EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights)
BEGIN
SELECT TOP 1 *
FROM Product
WHERE Weight > @re
AND Weight - @re < @Weights
END
ELSE
BEGIN
SELECT * FROM Product
WHERE CHARINDEX(',' + ID + ',', @ids) > 0
END
GO
UNION ALL SELECT 'B', 23
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 3
UNION ALL SELECT 'E', 27.9
UNION ALL SELECT 'H',15.1
UNION ALL SELECT 'F', 34
ID varchar(10),
Weight numeric(10,2)
)insert @Product
select
'A', 15.11
union all select
'B', 14.22
union all select
'C', 5.33
union all select
'D', 20.44
union all select
'E', 6.55
union all select
'F', 13.66
union all select
'G', 7.77
union all select
'H', 20.88
declare @Weight numeric(10,2)select @Weight=sum(weight)*0.2 from @Product
print @Weightdeclare @t table (
IDs varchar(1000),
Weight numeric(10,2)
)insert @t
select ','+id,weight from @Product
where Weight<=@Weightwhile not exists (select 1 from @t where weight=@Weight)
and exists (
select 1 from @t t,@Product p
where t.ids not like '%,'+p.id+'%'
and t.weight+p.weight<=@weight
and not exists (select 1 from @t where ids=t.ids+','+p.id)
)
begin
insert @t
select t.ids+','+p.id,t.weight+p.weight
from @t t,@Product p
where t.ids not like '%,'+p.id+'%'
and t.weight+p.weight<=@weight
and not exists (select 1 from @t where ids=t.ids+','+p.id)
endif exists (select 1 from @t where weight=@Weight)
select * from @t where weight=@Weightselect top 1 t.ids+','+p.id as ids,t.weight+p.weight as weight
from @t t,@Product p
where t.ids not like '%,'+p.id+'%'
and not exists (select 1 from @t where ids=t.ids+','+p.id)
order by t.weight+p.weight
结果:
IDs Weight
--------------------------------- ------------
,G,F 21.43(所影响的行数为 1 行)
select * from @t where weight=@Weightselect top 1 t.ids+','+p.id as ids,t.weight+p.weight as weight
from @t t,@Product p
where t.ids not like '%,'+p.id+'%'
and not exists (select 1 from @t where ids=t.ids+','+p.id)
order by t.weight+p.weight -->if exists (select 1 from @t where weight=@Weight)
select * from @t where weight=@Weight
else
select top 1 t.ids+','+p.id as ids,t.weight+p.weight as weight
from @t t,@Product p
where t.ids not like '%,'+p.id+'%'
and not exists (select 1 from @t where ids=t.ids+','+p.id)
order by t.weight+p.weight
SELECT 'A', 34
UNION ALL SELECT 'B', 23
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 3
UNION ALL SELECT 'E', 27.9
UNION ALL SELECT 'H',15.1
UNION ALL SELECT 'F', 34sum*0.2=27.8 ,所以应该选择E,27.9结果按照你的sql语句,b,c,d 为28不是最佳。
ID varchar(10),
Weight numeric(10,2)
)insert @Product
SELECT 'A', 34
UNION ALL SELECT 'B', 23
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 3
UNION ALL SELECT 'E', 27.9
UNION ALL SELECT 'F', 34
UNION ALL SELECT 'G', 15.1/*
select
'A', 15.11
union all select
'B', 14.22
union all select
'C', 5.33
union all select
'D', 20.44
union all select
'E', 6.55
union all select
'F', 13.66
union all select
'G', 7.77
union all select
'H', 20.88
*/declare @Weight numeric(10,2)select @Weight=sum(weight)*0.2 from @Product
print @Weightdeclare @t table (
IDs varchar(1000),
Weight numeric(10,2)
)insert @t
select ','+id,weight from @Product
where Weight<=@Weightwhile not exists (select 1 from @t where weight=@Weight)
and exists (
select 1 from @t t,@Product p
where t.ids not like '%,'+p.id+'%'
and t.weight+p.weight<=@weight
and not exists (select 1 from @t where ids=t.ids+','+p.id)
)
begin
insert @t
select t.ids+','+p.id,t.weight+p.weight
from @t t,@Product p
where t.ids not like '%,'+p.id+'%'
and t.weight+p.weight<=@weight
and not exists (select 1 from @t where ids=t.ids+','+p.id)
endif exists (select 1 from @t where weight=@Weight)
select * from @t where weight=@Weight
else
select top 1 *
from (
select ','+id as ids,weight from @Product where weight>@weight
union all
select t.ids+','+p.id as ids,t.weight+p.weight as weight
from @t t,@Product p
where t.ids not like '%,'+p.id+'%'
and not exists (select 1 from @t where ids=t.ids+','+p.id)
) as t
order by weight,len(ids)
CREATE TABLE Product(ID char(1), Weight DECIMAL(18, 2))
INSERT Product
SELECT 'A', 34
UNION ALL SELECT 'B', 23
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 3
UNION ALL SELECT 'E', 27.9
UNION ALL SELECT 'H', 15.1
UNION ALL SELECT 'F', 34
GO-- 查询处理
DECLARE @re decimal(18, 2)
SELECT @re = SUM(Weight) * .2 FROM Product
IF EXISTS(SELECT * FROM Product WHERE Weight = @re)
BEGIN
SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight = @re
RETURN
END
DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), ID char(1), Weight DECIMAL(18, 2))
INSERT @chk SELECT
',' + A.ID + ',' + B.ID + ',',
A.Weight + B.Weight,
B.ID, B.Weight
FROM Product A, Product B
WHERE A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
WHILE @@ROWCOUNT = 0
BEGIN
IF EXISTS(SELECT * FROM @chk WHERE Weight = @re)
BEGIN
SELECT [20%] = @re, * FROM Product
WHERE CHARINDEX(QUOTENAME(ID, ','), (SELECT TOP 1 IDS FROM @chk WHERE Weight = @re)) > 0
RETURN
END
UPDATE A SET
IDS = A.IDS + B.ID + ',',
Weights = A.Weights + B.Weight,
ID = B.ID,
Weight = B.Weight
FROM @chk A, Product B
WHERE A.Weight < @re
AND A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2)
SELECT TOP 1 @ids = IDS, @Weights = ABS(Weights - @re)
FROM(
SELECT
IDS = A.IDS + B.ID + ',',
Weights = A.Weights + B.Weight,
ID = B.ID,
Weight = B.Weight
FROM @chk A, Product B
WHERE A.Weight < @re
AND A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
)A
ORDER BY Weights - @reIF EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights)
BEGIN
SELECT TOP 1 [20%] = @re, *
FROM Product
WHERE Weight > @re
AND Weight - @re < @Weights
ORDER BY Weight
END
ELSE
BEGIN
SELECT [20%] = @re, * FROM Product
WHERE CHARINDEX(',' + ID + ',', @ids) > 0
END
GODROP TABLE Product-- 结果
20% ID Weight
--------------------------------------- ---- ---------------------------------------
27.80 E 27.90(1 行受影响)
同时修正了一个排序的问题
UNION ALL SELECT 'B', 23
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 3
UNION ALL SELECT 'E', 27.3
UNION ALL SELECT 'F', 0.5
UNION ALL SELECT 'G', 0.1
UNION ALL SELECT 'H', 15.1
UNION ALL SELECT 'I', 34
CREATE TABLE Product(ID char(1), Weight DECIMAL(18, 2))
INSERT Product
SELECT 'A', 34
UNION ALL SELECT 'B', 23
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 3
UNION ALL SELECT 'E', 27.3
UNION ALL SELECT 'F', 0.5
UNION ALL SELECT 'G', 0.1
UNION ALL SELECT 'H', 15.1
UNION ALL SELECT 'I', 34
GO-- 查询处理
DECLARE @re decimal(18, 2)
SELECT @re = SUM(Weight) * .2 FROM Product
IF EXISTS(SELECT * FROM Product WHERE Weight = @re)
BEGIN
SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight = @re
RETURN
END
DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), ID char(1), Weight DECIMAL(18, 2))
INSERT @chk SELECT
',' + A.ID + ',' + B.ID + ',',
A.Weight + B.Weight,
B.ID, B.Weight
FROM Product A, Product B
WHERE A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
WHILE @@ROWCOUNT > 0
BEGIN
IF EXISTS(SELECT * FROM @chk WHERE Weights = @re)
BEGIN
SELECT [20%] = @re, * FROM Product
WHERE CHARINDEX(',' + ID + ',', (SELECT TOP 1 IDS FROM @chk WHERE Weights = @re)) > 0
RETURN
END
UPDATE A SET
IDS = A.IDS + B.ID + ',',
Weights = A.Weights + B.Weight,
ID = B.ID,
Weight = B.Weight
FROM @chk A, Product B
WHERE A.Weight < @re
AND A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2)
SELECT TOP 1 @ids = IDS, @Weights = Weights - @re
FROM(
SELECT
IDS = A.IDS + B.ID + ',',
Weights = A.Weights + B.Weight,
ID = B.ID,
Weight = B.Weight
FROM @chk A, Product B
WHERE A.Weight < @re
AND A.Weight + B.Weight >= @re
)A
ORDER BY Weights - @reIF EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights)
BEGIN
SELECT TOP 1 [20%] = @re, *
FROM Product
WHERE Weight > @re
AND Weight - @re < @Weights
ORDER BY Weight
END
ELSE
BEGIN
SELECT [20%] = @re, * FROM Product
WHERE CHARINDEX(',' + ID + ',', @ids) > 0
END
GODROP TABLE Product-- 结果
20% ID Weight
--------------------------------------- ---- ----------
27.80 E 27.30
27.80 F 0.50(2 行受影响)
里面判断相等的也用错了, 应该用: Weights 列
UNION ALL SELECT 'B', 23
UNION ALL SELECT 'C', 24
UNION ALL SELECT 'D', 3这个怎么没有结果?
CREATE TABLE Product(ID char(1), Weight DECIMAL(18, 2))
INSERT Product
SELECT 'A', 34
UNION ALL SELECT 'B', 23
UNION ALL SELECT 'C', 24
UNION ALL SELECT 'D', 3
GO-- 查询处理
DECLARE @re decimal(18, 2)
SELECT @re = SUM(Weight) * .2 FROM Product
IF EXISTS(SELECT * FROM Product WHERE Weight = @re)
BEGIN
SELECT TOP 1 [20%] = @re, * FROM Product WHERE Weight = @re
RETURN
END
DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), ID char(1), Weight DECIMAL(18, 2))
INSERT @chk SELECT
',' + A.ID + ',' + B.ID + ',',
A.Weight + B.Weight,
B.ID, B.Weight
FROM Product A, Product B
WHERE A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
WHILE @@ROWCOUNT > 0
BEGIN
IF EXISTS(SELECT * FROM @chk WHERE Weights = @re)
BEGIN
SELECT [20%] = @re, * FROM Product
WHERE CHARINDEX(',' + ID + ',', (SELECT TOP 1 IDS FROM @chk WHERE Weights = @re)) > 0
RETURN
END
UPDATE A SET
IDS = A.IDS + B.ID + ',',
Weights = A.Weights + B.Weight,
ID = B.ID,
Weight = B.Weight
FROM @chk A, Product B
WHERE A.Weight < @re
AND A.Weight + B.Weight <= @re
AND(A.Weight > B.Weight
OR A.Weight = B.Weight AND A.ID < B.ID)
ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2)
SELECT TOP 1 @ids = IDS, @Weights = Weights - @re
FROM(
SELECT
IDS = A.IDS + B.ID + ',',
Weights = A.Weights + B.Weight,
ID = B.ID,
Weight = B.Weight
FROM @chk A, Product B
WHERE A.Weight < @re
AND A.Weight + B.Weight >= @re
)A
ORDER BY Weights - @reIF @@ROWCOUNT = 0
OR EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights)
BEGIN
SELECT TOP 1 [20%] = @re, *
FROM Product
WHERE Weight > @re
AND Weight - @re < ISNULL(@Weights, @re)
ORDER BY Weight
END
ELSE
BEGIN
SELECT [20%] = @re, * FROM Product
WHERE CHARINDEX(',' + ID + ',', @ids) > 0
END
GODROP TABLE Product-- 结果
20% ID Weight
--------------------------------------- ---- ---------------------------------------
16.80 B 23.00(1 行受影响)
UNION ALL SELECT 'B', 2
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 3
UNION ALL SELECT 'e', 3
UNION ALL SELECT 'f', 3
UNION ALL SELECT 'g', 33
UNION ALL SELECT 'h', 34
UNION ALL SELECT 'i', 3
CREATE TABLE Product(ID char(1), Weight DECIMAL(18, 2))
INSERT Product
SELECT 'A', 3
UNION ALL SELECT 'B', 2
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 3
UNION ALL SELECT 'e', 3
UNION ALL SELECT 'f', 3
UNION ALL SELECT 'g', 33
UNION ALL SELECT 'h', 34
UNION ALL SELECT 'i', 3
GO-- 查询处理
DECLARE @re decimal(18, 2)
SELECT @re = SUM(Weight) * .2 FROM ProductDECLARE @l int
SET @l = 0
DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), Level int)
INSERT @chk SELECT ',' + A.ID + ',', A.Weight, @l
FROM Product A
WHERE A.Weight <= @re
WHILE @@ROWCOUNT > 0
BEGIN
IF EXISTS(SELECT * FROM @chk WHERE Level = @l AND Weights = @re)
BEGIN
SELECT [20%] = @re, * FROM Product
WHERE CHARINDEX(',' + ID + ',', (SELECT TOP 1 IDS FROM @chk WHERE Level = @l AND Weights = @re)) > 0
RETURN
END SET @l = @l + 1
INSERT @chk SELECT
A.IDS + B.ID + ',',
A.Weights + B.Weight,
@l
FROM @chk A, Product B
WHERE A.Level = @l - 1
AND A.Weights + B.Weight <= @re
AND CHARINDEX(',' + B.ID + ',', A.IDS) = 0
ENDDECLARE @ids varchar(8000), @Weights decimal(18, 2)
SELECT TOP 1
@ids = IDS, @Weights = @re - Weights
FROM @chk
ORDER BY Weights DESCIF @@ROWCOUNT = 0
OR EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights)
BEGIN
SELECT TOP 1 [20%] = @re, *
FROM Product
WHERE Weight > @re
AND Weight - @re < ISNULL(@Weights, @re)
ORDER BY Weight
END
ELSE
BEGIN
SELECT [20%] = @re, * FROM Product
WHERE CHARINDEX(',' + ID + ',', @ids) > 0
UNION ALL
SELECT * FROM(
SELECT TOP 1
[20%] = @re, * FROM Product
WHERE CHARINDEX(',' + ID + ',', @ids) = 0
AND Weight >= @Weights
ORDER BY Weight
)A
END
GODROP TABLE Product
SELECT 'A', 3
UNION ALL SELECT 'B', 42
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 12
UNION ALL SELECT 'e', 3
UNION ALL SELECT 'f', 3
UNION ALL SELECT 'g', 33
UNION ALL SELECT 'h', 34
UNION ALL SELECT 'i', 3
GO
结果是,实际上只要选择g,33就可以了
.
27.00 A 3.00
27.00 C 2.00
27.00 D 12.00
27.00 e 3.00
27.00 f 3.00
27.00 i 3.00
27.00 g 33.00
CREATE TABLE Product(ID char(1), Weight DECIMAL(18, 2))
INSERT Product
SELECT 'A', 3
UNION ALL SELECT 'B', 42
UNION ALL SELECT 'C', 2
UNION ALL SELECT 'D', 12
UNION ALL SELECT 'e', 3
UNION ALL SELECT 'f', 3
UNION ALL SELECT 'g', 33
UNION ALL SELECT 'h', 34
UNION ALL SELECT 'i', 3
GO-- 查询处理
DECLARE @re decimal(18, 2)
SELECT @re = SUM(Weight) * .2 FROM ProductDECLARE @l int
SET @l = 0
DECLARE @chk TABLE(IDS varchar(8000), Weights decimal(18, 2), Level int)
INSERT @chk SELECT ',' + A.ID + ',', A.Weight, @l
FROM Product A
WHERE A.Weight <= @re
WHILE @@ROWCOUNT > 0
BEGIN
IF EXISTS(SELECT * FROM @chk WHERE Level = @l AND Weights = @re)
BEGIN
SELECT [20%] = @re, * FROM Product
WHERE CHARINDEX(',' + ID + ',', (SELECT TOP 1 IDS FROM @chk WHERE Level = @l AND Weights = @re)) > 0
RETURN
END SET @l = @l + 1
INSERT @chk SELECT
A.IDS + B.ID + ',',
A.Weights + B.Weight,
@l
FROM @chk A, Product B
WHERE A.Level = @l - 1
AND A.Weights + B.Weight <= @re
AND CHARINDEX(',' + B.ID + ',', A.IDS) = 0
ENDINSERT @chk SELECT
A.IDS + B.ID + ',',
A.Weights + B.Weight,
@l
FROM @chk A, Product B
WHERE A.Level = @l - 1
AND A.Weights + B.Weight >= @re
AND CHARINDEX(',' + B.ID + ',', A.IDS) = 0DECLARE @ids varchar(8000), @Weights decimal(18, 2)
SELECT TOP 1
@ids = IDS, @Weights = Weights - @re
FROM @chk
WHERE Weights > @re
ORDER BY WeightsIF @@ROWCOUNT = 0
OR EXISTS(SELECT * FROM Product WHERE Weight > @re AND Weight - @re < @Weights)
BEGIN
SELECT TOP 1 [20%] = @re, *
FROM Product
WHERE Weight > @re
AND Weight - @re < ISNULL(@Weights, @re)
ORDER BY Weight
END
ELSE
BEGIN
SELECT [20%] = @re, * FROM Product
WHERE CHARINDEX(',' + ID + ',', @ids) > 0
END
GODROP TABLE Product
UNION ALL SELECT 'B', 42
UNION ALL SELECT 'C', 22
UNION ALL SELECT 'D', 12
UNION ALL SELECT 'e', 32
UNION ALL SELECT 'f', 33
UNION ALL SELECT 'g', 33
UNION ALL SELECT 'h', 34
UNION ALL SELECT 'i', 3结果出来是这个,应该A+b,或b+i 45
42.80 A 3.00
42.80 C 22.00
42.80 D 12.00
42.80 e 32.00
42.80 i 3.00
如Product(Id char(1),Weight decimal(9,2)) 有20条记录的话,总重量1000kg,如果要求满足条件1+2,及sum*0.2=400kg 且记录数最少,按照我以前的思路处理如下:存在一条记录就满足条件的@sum0.2,放到一个临时表中select top 1 * into #temp1 from Product where [email protected] 存在2条记录重量和满足条件@sum0.2 ,插入到临时表中
insert into #temp1
select top 1 a.id+b.id, a.weight+b.wiehgt
from product a ,product b
where a.id<>b.id and a.weight+b.weight>[email protected]
order by a.weight+b.weight desc存在3条记录重量之和满足@sum0.2
insert into #temp1
select top 1 a.id+b.id+c.id, a.weight+b.weihgt+c.weight
from product a ,product b ,product c
where a.id<>b.id and a.id<>c.id and b.id<>c.id a.weight+b.weight+c.weight>[email protected]
order by a.weight+b.weight+c.weight desc...存在n条记录记录满足条件,n是有上限的,即product列表中重量从小到大排序,前面n条记录
之和>[email protected]就可以了。最终结果是select top 1 * from #temp1 order by weight desc
,就是最佳方案了。所以这里只能处理数十条记录,多了的话服务器承受不了了。以上是我的原始求解法,答案是正确的,但是效率非常低下。
如Product(Id char(1),Weight decimal(9,2)) 有20条记录的话,总重量1000kg,如果要求满足条件1+2,及sum*0.2=400kg 且记录数最少,按照我以前的思路处理如下:存在一条记录就满足条件的@sum0.2,放到一个临时表中select top 1 * into #temp1 from Product where weight>[email protected] 存在2条记录重量和满足条件@sum0.2 ,插入到临时表中
insert into #temp1
select top 1 a.id+b.id, a.weight+b.wiehgt
from product a ,product b
where a.id<>b.id and a.weight+b.weight>[email protected]
order by a.weight+b.weight desc存在3条记录重量之和满足@sum0.2
insert into #temp1
select top 1 a.id+b.id+c.id, a.weight+b.weihgt+c.weight
from product a ,product b ,product c
where a.id<>b.id and a.id<>c.id and b.id<>c.id a.weight+b.weight+c.weight>[email protected]
order by a.weight+b.weight+c.weight desc...存在n条记录记录满足条件,n是有上限的,即product列表中重量从小到大排序,前面n条记录
之和>[email protected]就可以了。最终结果是select top 1 * from #temp1 order by weight desc
,就是最佳方案了。所以这里只能处理数十条记录,多了的话服务器承受不了了。以上是我的原始求解法,答案是正确的,但是效率非常低下。
所以提出来大家帮我想想办法,提供一段C#或Delphi代码给我可以解决这个问题。