【请教】一个产品平均打折的问题...(二)

[outwindows]

产品价格表:
----------------------------------------------------
产品名称 最低价　　最高价 最低打折率(%) 订价
P1 110 300 36.67
P2 150 250 60.00
P3 100 200 50.00
P4 120 300 40.00
总计 480 1050
--------------------------------------------------
实际价格600,最高价1050，所以实际总打折率600/1050 = 0.5714,那么订价=最高价

*0.5714:
产品名称 最低价　　最高价 最低打折率(%) 打折(%) 订价
P1 110 300 36.67 57.14 171.42
P2 150 250 60.00 57.14 142.85
P3 100 200 50.00 57.14 114.28
P4 120 300 40.00 57.14 171.42
--------------------------------------------------
但是这样一来，产品P2的打折过低，订价低于了最低价，需重新进行筛选,先把P2订价设为

最低价(即150)，再以同样的算法去算其它产品的订价：
其它产品的实际价格为600-150=450，其它产品的最高价为1050-250=800，
这样又以实际价格450，最高价800为基础按相同的方法去算剩余3个产品的订价:
--------------------------------------------------
产品名称 最低价　　最高价
P2 150 250

[outwindows]

--表结构及测试数据...
----------------------------------------------------------------------
DROP TABLE [BAS_PRODUCT]
CREATE TABLE [BAS_PRODUCT] ( [BAS_ID] [int] IDENTITY (1, 1) NOT NULL , [BAS_NAME] [varchar] (20) NULL , [MIN_PRICE] [float] NULL , [MAX_PRICE] [float] NULL , [ORDER_PRICE] [float] NULL )
ALTER TABLE [BAS_PRODUCT] WITH NOCHECK ADD CONSTRAINT [PK_BAS_PRODUCT] PRIMARY KEY NONCLUSTERED ( [BAS_ID] )

SET IDENTITY_INSERT [BAS_PRODUCT] ON

INSERT [BAS_PRODUCT] ( [BAS_ID] , [BAS_NAME] , [MIN_PRICE] , [MAX_PRICE] ) VALUES ( 1 , 'P1' , 110.0 , 300.0 )
INSERT [BAS_PRODUCT] ( [BAS_ID] , [BAS_NAME] , [MIN_PRICE] , [MAX_PRICE] ) VALUES ( 2 , 'P2' , 150.0 , 250.0 )
INSERT [BAS_PRODUCT] ( [BAS_ID] , [BAS_NAME] , [MIN_PRICE] , [MAX_PRICE] ) VALUES ( 3 , 'P3' , 100.0 , 200.0 )
INSERT [BAS_PRODUCT] ( [BAS_ID] , [BAS_NAME] , [MIN_PRICE] , [MAX_PRICE] ) VALUES ( 4 , 'P4' , 120.0 , 300.0 )

SET IDENTITY_INSERT [BAS_PRODUCT] OFF

[outwindows]

------------------------------------------------------------------------------
回复人： i9988(冒牌j9988 V0.1) ( ) 信誉：100 2004-07-16 10:07:00 得分: 0


没有答案？

我抛块砖吧：

create proc Get_ORDER_PRICE
@All_ORDER_PRICE [float]
as

--最高价
declare @All_MAX_PRICE [float]
select @All_MAX_PRICE=sum(MAX_PRICE) from BAS_PRODUCT

--平均折扣
declare @Avg_Cent [float]
set @Avg_Cent=@All_ORDER_PRICE/@All_MAX_PRICE

--剩余
declare @Lost [float]
set @Lost=0

update BAS_PRODUCT
set ORDER_PRICE=case when MAX_PRICE*@Avg_Cent>=MIN_PRICE then MAX_PRICE*@Avg_Cent else MIN_PRICE end,
@Lost=@Lost+case when MAX_PRICE*@Avg_Cent>=MIN_PRICE then 0 else MIN_PRICE-MAX_PRICE*@Avg_Cent end

declare @All_ORDER_PRICE1 [float]
declare @All_MAX_PRICE1 [float]
declare @Avg_Cent1 [float]
declare @Lost1 [float]

while @Lost>0
begin
set @All_ORDER_PRICE1=@All_ORDER_PRICE-(select sum(MIN_PRICE) from BAS_PRODUCT where MIN_PRICE=ORDER_PRICE)

--最高价
select @All_MAX_PRICE1=sum(MAX_PRICE) from BAS_PRODUCT where MIN_PRICE<ORDER_PRICE

--平均折扣
set @Avg_Cent1=@All_ORDER_PRICE1/@All_MAX_PRICE1

--剩余
set @Lost1=0

update BAS_PRODUCT
set ORDER_PRICE=case when MAX_PRICE*@Avg_Cent1>=MIN_PRICE then MAX_PRICE*@Avg_Cent1 else MIN_PRICE end,
@Lost1=@Lost1+case when MAX_PRICE*@Avg_Cent1>=MIN_PRICE then 0 else MIN_PRICE-MAX_PRICE*@Avg_Cent1 end
where MIN_PRICE<ORDER_PRICE
set @Lost=@Lost1
end
go

--运行
exec Get_ORDER_PRICE 600

--验证
select sum(ORDER_PRICE) from BAS_PRODUCT
/*
结果：

-----------------------------------------------------
600.0

（所影响的行数为 1 行）
*/

--验证1
select * from BAS_PRODUCT
/*
结果：
BAS_ID BAS_NAME MIN_PRICE MAX_PRICE ORDER_PRICE
----------- -------------------- ----------------------------------------------------- ----------------------------------------------------- -----------------------------------------------------
1 P1 110.0 300.0 168.75
2 P2 150.0 250.0 150.0
3 P3 100.0 200.0 112.5
4 P4 120.0 300.0 168.75

（所影响的行数为 4 行）

*/

--验证2
select *,100*MIN_PRICE/Max_PRICE as [最低打折率(%)],100*ORDER_PRICE/Max_PRICE as [打折(%)] from BAS_PRODUCT
/*
结果：
BAS_ID BAS_NAME MIN_PRICE MAX_PRICE ORDER_PRICE 最低打折率(%) 打折(%)
----------- -------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- ----------------------------------------------------- -----------------------------------------------------
1 P1 110.0 300.0 168.75 36.666666666666664 56.25
2 P2 150.0 250.0 150.0 60.0 60.0
3 P3 100.0 200.0 112.5 50.0 56.25
4 P4 120.0 300.0 168.75 40.0 56.25

（所影响的行数为 4 行）

*/
------------------------------------------------------------------------------
回复人： i9988(冒牌j9988 V0.1) ( ) 信誉：100 2004-07-16 10:19:00 得分: 0


加检查参数（例子的参数应该在480和1050之间）：

alter proc Get_ORDER_PRICE
@All_ORDER_PRICE [float]
as
--最高价
declare @All_MAX_PRICE [float]
select @All_MAX_PRICE=sum(MAX_PRICE) from BAS_PRODUCT

--检查参数
IF @All_ORDER_PRICE<(select sum(MIN_PRICE) from BAS_PRODUCT)
OR @All_ORDER_PRICE>@All_MAX_PRICE
BEGIN
PRINT '参数错误'
RETURN
END

--平均折扣
declare @Avg_Cent [float]
set @Avg_Cent=@All_ORDER_PRICE/@All_MAX_PRICE

--剩余
declare @Lost [float]
set @Lost=0

update BAS_PRODUCT
set ORDER_PRICE=case when MAX_PRICE*@Avg_Cent>=MIN_PRICE then MAX_PRICE*@Avg_Cent else MIN_PRICE end,
@Lost=@Lost+case when MAX_PRICE*@Avg_Cent>=MIN_PRICE then 0 else MIN_PRICE-MAX_PRICE*@Avg_Cent end

declare @All_ORDER_PRICE1 [float]
declare @All_MAX_PRICE1 [float]
declare @Avg_Cent1 [float]
declare @Lost1 [float]

while @Lost>0
begin
set @All_ORDER_PRICE1=@All_ORDER_PRICE-(select sum(MIN_PRICE) from BAS_PRODUCT where MIN_PRICE=ORDER_PRICE)

--最高价
select @All_MAX_PRICE1=sum(MAX_PRICE) from BAS_PRODUCT where MIN_PRICE<ORDER_PRICE

--平均折扣
set @Avg_Cent1=@All_ORDER_PRICE1/@All_MAX_PRICE1

--剩余
set @Lost1=0

update BAS_PRODUCT
set ORDER_PRICE=case when MAX_PRICE*@Avg_Cent1>=MIN_PRICE then MAX_PRICE*@Avg_Cent1 else MIN_PRICE end,
@Lost1=@Lost1+case when MAX_PRICE*@Avg_Cent1>=MIN_PRICE then 0 else MIN_PRICE-MAX_PRICE*@Avg_Cent1 end
where MIN_PRICE<ORDER_PRICE
set @Lost=@Lost1
end

[outwindows]

回复人： i9988(冒牌j9988 V0.1) ( ) 信誉：100 2004-07-16 10:46:00 得分: 0


另一算法，从另一头算

CREATE proc Get_ORDER_PRICE1
@All_ORDER_PRICE [float]
as
--最高价
declare @All_MAX_PRICE [float]
select @All_MAX_PRICE=sum(MAX_PRICE) from BAS_PRODUCT

--最低价
declare @All_MIN_PRICE [float]
select @All_MIN_PRICE=sum(MIN_PRICE) from BAS_PRODUCT

--检查参数
IF @All_ORDER_PRICE<@All_MIN_PRICE
OR @All_ORDER_PRICE>@All_MAX_PRICE
BEGIN
PRINT '参数错误'
RETURN
END

--平均折扣,如果平均折扣理解为在最低价加一个比例，会怎么样？
declare @Avg_Cent [float]
set @Avg_Cent=@All_ORDER_PRICE/@All_MIN_PRICE

--剩余
declare @Lost [float]
set @Lost=0

update BAS_PRODUCT
set ORDER_PRICE=case when MIN_PRICE*@Avg_Cent<=MAX_PRICE then MIN_PRICE*@Avg_Cent else MAX_PRICE end,
@Lost=@Lost+case when MIN_PRICE*@Avg_Cent<=MAX_PRICE then 0 else MIN_PRICE*@Avg_Cent-MAX_PRICE end


declare @All_ORDER_PRICE1 [float]
declare @All_MIN_PRICE1 [float]
declare @Avg_Cent1 [float]
declare @Lost1 [float]

while @Lost>0
begin
PRINT @LOST
set @All_ORDER_PRICE1=@All_ORDER_PRICE-(select sum(MAX_PRICE) from BAS_PRODUCT where MAX_PRICE=ORDER_PRICE)

--最低价
select @All_MIN_PRICE1=sum(MIN_PRICE) from BAS_PRODUCT where MAX_PRICE>ORDER_PRICE

--平均折扣
set @Avg_Cent1=@All_ORDER_PRICE1/@All_MIN_PRICE1

--剩余
set @Lost1=0

update BAS_PRODUCT
set ORDER_PRICE=case when MIN_PRICE*@Avg_Cent1<=MAX_PRICE then MIN_PRICE*@Avg_Cent1 else MAX_PRICE end,
@Lost1=@Lost1+case when MIN_PRICE*@Avg_Cent1<=MAX_PRICE then 0 else MIN_PRICE*@Avg_Cent1-MAX_PRICE end
where MAX_PRICE>ORDER_PRICE
set @Lost=@Lost1
end

[i9988]

既然新开贴，我把以前的整理在一起，免得糊涂了

(算法一，楼主说的算法)

CREATE proc Get_ORDER_PRICE
@All_ORDER_PRICE [float]
as
--最高价
declare @All_MAX_PRICE [float]
select @All_MAX_PRICE=sum(MAX_PRICE) from BAS_PRODUCT

--检查参数
IF @All_ORDER_PRICE<(select sum(MIN_PRICE) from BAS_PRODUCT)
OR @All_ORDER_PRICE>@All_MAX_PRICE
BEGIN
PRINT '参数错误'
RETURN
END

--平均折扣
declare @Avg_Cent [float]
set @Avg_Cent=@All_ORDER_PRICE/@All_MAX_PRICE

--剩余
declare @Lost [float]
set @Lost=0

update BAS_PRODUCT
set ORDER_PRICE=case when MAX_PRICE*@Avg_Cent>=MIN_PRICE then MAX_PRICE*@Avg_Cent else MIN_PRICE end,
@Lost=@Lost+case when MAX_PRICE*@Avg_Cent>=MIN_PRICE then 0 else MIN_PRICE-MAX_PRICE*@Avg_Cent end

declare @All_ORDER_PRICE1 [float]
declare @All_MAX_PRICE1 [float]
declare @Avg_Cent1 [float]
declare @Lost1 [float]

while @Lost>0
begin
set @All_ORDER_PRICE1=@All_ORDER_PRICE-(select sum(MIN_PRICE) from BAS_PRODUCT where MIN_PRICE=ORDER_PRICE)

--最高价
select @All_MAX_PRICE1=sum(MAX_PRICE) from BAS_PRODUCT where MIN_PRICE<ORDER_PRICE

--平均折扣
set @Avg_Cent1=@All_ORDER_PRICE1/@All_MAX_PRICE1

--剩余
set @Lost1=0

update BAS_PRODUCT
set ORDER_PRICE=case when MAX_PRICE*@Avg_Cent1>=MIN_PRICE then MAX_PRICE*@Avg_Cent1 else MIN_PRICE end,
@Lost1=@Lost1+case when MAX_PRICE*@Avg_Cent1>=MIN_PRICE then 0 else MIN_PRICE-MAX_PRICE*@Avg_Cent1 end
where MIN_PRICE<ORDER_PRICE
set @Lost=@Lost1
end
GO

[outwindows]

回复人： zjcxc(邹建) ( ) 信誉：349 2004-07-16 11:27:00 得分: 0


--计算的存储过程
create proc p_calc
@all_order_price float=600,
@reset bit=1 --是否重新初始化 ORDER_PRICE 的值
as
declare @avg_price float

if @reset=0 update BAS_PRODUCT set ORDER_PRICE=null
--实际上,如果 ORDER_PRICE 默认值设置为 0,这里更新为0的话,效率会更高
--当然,如果默认值设置为0的话,则下面所有的条件中,将 is null 换成=0

select @avg_price=@all_order_price/sum(MAX_PRICE)
from BAS_PRODUCT
where ORDER_PRICE is null --只计算没有值的,这样可以在某个结果的基础上再算

update BAS_PRODUCT set ORDER_PRICE=MIN_PRICE
,@all_order_price=@all_order_price-MIN_PRICE
where ORDER_PRICE is null
and MIN_PRICE/MAX_PRICE>@avg_price
while @@rowcount>0
begin
select @avg_price=@all_order_price/sum(MAX_PRICE)
from BAS_PRODUCT
where ORDER_PRICE is null --只计算没有值的,这样可以在某个结果的基础上再算

update BAS_PRODUCT set ORDER_PRICE=MIN_PRICE
,@all_order_price=@all_order_price-MIN_PRICE
where ORDER_PRICE is null
and MIN_PRICE/MAX_PRICE>@avg_price
end
if @all_order_price>0
update BAS_PRODUCT set ORDER_PRICE=MAX_PRICE*@avg_price
where ORDER_PRICE is null
go

--调用存储过程进行计算
exec p_calc

[i9988]

楼主别贴了，我来整理吧

另一算法，从另一头算
CREATE proc Get_ORDER_PRICE1
@All_ORDER_PRICE [float]
as
--最高价
declare @All_MAX_PRICE [float]
select @All_MAX_PRICE=sum(MAX_PRICE) from BAS_PRODUCT

--最低价
declare @All_MIN_PRICE [float]
select @All_MIN_PRICE=sum(MIN_PRICE) from BAS_PRODUCT

--检查参数
IF @All_ORDER_PRICE<@All_MIN_PRICE
OR @All_ORDER_PRICE>@All_MAX_PRICE
BEGIN
PRINT '参数错误'
RETURN
END

--平均折扣,如果平均折扣理解为在最低价加一个比例，会怎么样？
declare @Avg_Cent [float]
set @Avg_Cent=@All_ORDER_PRICE/@All_MIN_PRICE

--剩余
declare @Lost [float]
set @Lost=0

update BAS_PRODUCT
set ORDER_PRICE=case when MIN_PRICE*@Avg_Cent<=MAX_PRICE then MIN_PRICE*@Avg_Cent else MAX_PRICE end,
@Lost=@Lost+case when MIN_PRICE*@Avg_Cent<=MAX_PRICE then 0 else MIN_PRICE*@Avg_Cent-MAX_PRICE end


declare @All_ORDER_PRICE1 [float]
declare @All_MIN_PRICE1 [float]
declare @Avg_Cent1 [float]
declare @Lost1 [float]

while @Lost>0
begin
set @All_ORDER_PRICE1=@All_ORDER_PRICE-(select sum(MAX_PRICE) from BAS_PRODUCT where MAX_PRICE=ORDER_PRICE)

--最低价
select @All_MIN_PRICE1=sum(MIN_PRICE) from BAS_PRODUCT where MAX_PRICE>ORDER_PRICE

--平均折扣
set @Avg_Cent1=@All_ORDER_PRICE1/@All_MIN_PRICE1

--剩余
set @Lost1=0

update BAS_PRODUCT
set ORDER_PRICE=case when MIN_PRICE*@Avg_Cent1<=MAX_PRICE then MIN_PRICE*@Avg_Cent1 else MAX_PRICE end,
@Lost1=@Lost1+case when MIN_PRICE*@Avg_Cent1<=MAX_PRICE then 0 else MIN_PRICE*@Avg_Cent1-MAX_PRICE end
where MAX_PRICE>ORDER_PRICE
set @Lost=@Lost1
end

[i9988]

根据参数所处的范围，选择算法（只调用这个存储过程）：

CREATE proc Get_ORDER_PRICE_SELECT
@All_ORDER_PRICE [float]
as
--最高价
declare @All_MAX_PRICE [float]
select @All_MAX_PRICE=sum(MAX_PRICE) from BAS_PRODUCT

--最低价
declare @All_MIN_PRICE [float]
select @All_MIN_PRICE=sum(MIN_PRICE) from BAS_PRODUCT

--检查参数
IF @All_ORDER_PRICE<@All_MIN_PRICE
OR @All_ORDER_PRICE>@All_MAX_PRICE
BEGIN
PRINT '参数错误'
RETURN
END

IF @All_ORDER_PRICE<=(@All_MIN_PRICE+@All_MAX_PRICE)/2
BEGIN
EXEC Get_ORDER_PRICE1 @All_ORDER_PRICE
END
ELSE
BEGIN
EXEC Get_ORDER_PRICE @All_ORDER_PRICE
END
GO

[i9988]

看看有没有更好的算法

[outwindows]

第一贴:
http://community.csdn.net/expert/Topicview1.asp?id=3179879

[zjcxc]

--建议楼主 ORDER_PRICE 的默认值设置为0,对应的我的存储过程改为:



--计算的存储过程
create proc p_calc
@all_order_price float=600, --实际价
@reset bit=1 --是否重新初始化 ORDER_PRICE 的值
as
declare @avg_price float

--如果需要,则重新初始化 ORDER_PRICE
if @reset=0 update BAS_PRODUCT set ORDER_PRICE=0

--得到当前打拆率
select @avg_price=@all_order_price/sum(MAX_PRICE)
from BAS_PRODUCT
where ORDER_PRICE=0 is null

--低于打拆率的更新为最低价
--同时处理 @all_order_price ,得到下一轮计算打拆率需要的实际价
update BAS_PRODUCT set ORDER_PRICE=MIN_PRICE
,@all_order_price=@all_order_price-MIN_PRICE
where ORDER_PRICE=0
and MIN_PRICE/MAX_PRICE>@avg_price

--如果有记录被更新,则继续处理
while @@rowcount>0
begin
--得到新轮的打拆率
select @avg_price=@all_order_price/sum(MAX_PRICE)
from BAS_PRODUCT
where ORDER_PRICE=0

--低于打拆率的更新为最低价
--同时处理 @all_order_price ,得到下一轮计算打拆率需要的实际价
update BAS_PRODUCT set ORDER_PRICE=MIN_PRICE
,@all_order_price=@all_order_price-MIN_PRICE
where ORDER_PRICE=0
and MIN_PRICE/MAX_PRICE>@avg_price
end

--如果实际价没有被全部处理掉,则更新打折没有低于最低价的数据
if @all_order_price>0
update BAS_PRODUCT set ORDER_PRICE=MAX_PRICE*@avg_price
where ORDER_PRICE=0
go

--调用存储过程进行计算
exec p_calc

[outwindows]

效率应该都查不多吧...

[zslhfdyx]

up...