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

Question

--表结构及测试数据...
----------------------------------------------------------------------
 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

Answer 1

产品价格表:
----------------------------------------------------
产品名称   最低价　　最高价   最低打折率(%)  订价
 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是怎么来的

Answer 2

没有答案？我抛块砖吧：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=0update 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 enddeclare @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 行）*/

Answer 3

--验证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 行）*/

Answer 4

加检查参数（例子的参数应该在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=0update 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 enddeclare @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

Answer 5

另一算法，从另一头算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=0update 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

Answer 6

而且,照你的算法,就出现pbsql所说的:第一次将打折高过平均折扣的产品筛选出来，再在剩余的产品中重新筛选...这样依次筛选下去总会有打折高过平均折扣的产品，岂不是全都筛选出来了？

Answer 7

--计算的存储过程
create proc p_calc
@all_order_price float=600,
@reset bit=1 --是否重新初始化 ORDER_PRICE 的值
as
declare @avg_price floatif @reset=0 update BAS_PRODUCT set ORDER_PRICE=null  
--实际上,如果 ORDER_PRICE 默认值设置为 0,这里更新为0的话,效率会更高
--当然,如果默认值设置为0的话,则下面所有的条件中,将 is null 换成=0select @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

Answer 8

--测试数据
if object_id('BAS_PRODUCT') is not null
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] ONINSERT [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
go--计算的存储过程
create proc p_calc
@all_order_price float=600,
@reset bit=1 --是否重新初始化 ORDER_PRICE 的值
as
declare @avg_price floatif @reset=0 update BAS_PRODUCT set ORDER_PRICE=null  
--实际上,如果 ORDER_PRICE 默认值设置为 0,这里更新为0的话,效率会更高
--当然,如果默认值设置为0的话,则下面所有的条件中,将 is null 换成=0select @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
go--显示计算结果
select * from BAS_PRODUCT
go--删除测试
drop proc p_calc
DROP TABLE [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 行）
--*/

Answer 9

declare @p float
set @p = 600
--1.先将产品打折率高于总的打折率的产品设为最低价
update bas_product set order_price=min_price
 where min_price/max_price>(@p/(select sum(max_price) from bas_product))--2.其它再平均
update bas_product set order_price=max_price*(@p-(select sum(order_price) from bas_product))/((select sum(max_price) from bas_product where isnull(order_price,0)=0))
where isnull(order_price,0)=0--验证
select sum(ORDER_PRICE) from BAS_PRODUCT
select * from BAS_PRODUCT
select *,100*MIN_PRICE/Max_PRICE as [最低打折率(%)],100*ORDER_PRICE/Max_PRICE as [打折(%)] from BAS_PRODUCT

Answer 10

outwindows(窗外) 的方法不对.

Answer 11

--这样的数据,实际价格为500来计算,outwindows(窗外) 的方法不对INSERT [BAS_PRODUCT] ( [BAS_ID] , [BAS_NAME] , [MIN_PRICE] , [MAX_PRICE] ) VALUES ( 1 , 'P1' , 100.0 , 450.0 )
INSERT [BAS_PRODUCT] ( [BAS_ID] , [BAS_NAME] , [MIN_PRICE] , [MAX_PRICE] ) VALUES ( 2 , 'P2' , 100.0 , 350.0 )
INSERT [BAS_PRODUCT] ( [BAS_ID] , [BAS_NAME] , [MIN_PRICE] , [MAX_PRICE] ) VALUES ( 3 , 'P3' , 100.0 , 250.0 )
INSERT [BAS_PRODUCT] ( [BAS_ID] , [BAS_NAME] , [MIN_PRICE] , [MAX_PRICE] ) VALUES ( 4 , 'P4' , 100.0 , 150.0 )

Answer 12

--测试结果BAS_ID  BAS_NAME MIN_PRICE MAX_PRICE ORDER_PRICE  最低打折率(%)    折率(%)
1 P1 110.0 300.0 125.0 36.666666666666664 41.666666666666664
2 P2 150.0 250.0 150.0 60.0 60.0
3 P3 100.0 200.0 100.0 50.0 50.0
4 P4 120.0 300.0 125.0 40.0 41.666666666666664

Answer 13

--2.其它再平均
update bas_product set order_price=
((min_price/max_price)/(select sum(min_price/max_price) 
                          from bas_product 
                         where isnull(order_price,0)=0))*
(@p-(select sum(order_price) from bas_product)) 
where isnull(order_price,0)=0

Answer 14

--测试结果BAS_ID  BAS_NAME MIN_PRICE MAX_PRICE ORDER_PRICE  最低打折率(%)    折率(%)
1 P1 110.0 300.0 119.56521739130436 36.666666666666664   39.855072463768117
2 P2 150.0 250.0 150.0 60.0 60.0
3 P3 100.0 200.0 100.0 50.0 50.0
4 P4 120.0 300.0 130.43478260869568 40.0 43.478260869565226

Answer 15

i9988 的也是这样...
BAS_ID  BAS_NAME MIN_PRICE MAX_PRICE ORDER_PRICE  最低打折率(%)    折率(%)
1 P1 110.0 300.0 125.0 36.666666666666664 41.666666666666664
2 P2 150.0 250.0 150.0 60.0 60.0
3 P3 100.0 200.0 100.0 50.0 50.0
4 P4 120.0 300.0 125.0 40.0 41.666666666666664

Answer 16

把两个方法结合起来，计算量会少些（试算次数少）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

Answer 17

语句没有错，i9988的也没有错，问题本身应该OK了...
现在就是合理是否的问题，偶自己来搞吧，谢谢大家，散...