declare @prod_id string,@pur_Qty int,@price intset @prod_id='200'
set @pur_qty=250
set @price=1000select sum(amount) as 应付金额 from
(
select sum(((case @pur_qty>end_number
when >0 then end_number else @pur_qty end
) as e_qty - start_number
)*commision*@price
) as amount
from 费率表
where product_id=@prod_id and @pur_qty>start_number
union
select (@pur_qty-max(end_number))*@price*默认费率
from 费率表
where prodcut_id=@prod_id
) T
set @pur_qty=250
set @price=1000select sum(amount) as 应付金额 from
(
select sum(((case @pur_qty>end_number
when >0 then end_number else @pur_qty end
) as e_qty - start_number
)*commision*@price
) as amount
from 费率表
where product_id=@prod_id and @pur_qty>start_number
union
select (@pur_qty-max(end_number))*@price*默认费率
from 费率表
where prodcut_id=@prod_id
) T
declare @编号 varchar(3),@数量 int,@单价 int,@默认费率 decimal(10,2)
declare @结束数量 int,@结果 decimal(38,4)
select @编号='200',@数量=350,@单价=1000,@默认费率=1
,@结果=0--计算处理
select @结束数量=case when @数量>end_number then end_number else @数量 end
,@结果=@结果+(@结束数量-start_number)*commision*@单价
from 费率表
where product_id=@编号if @结束数量<@数量 set @结果=@结果+(@数量-@结束数量)*@默认费率*@单价--显示结果
select 计算结果=@结果
declare @tb table(product_id varchar(3),start_number int,end_number int,commision decimal(20,1))
insert into @tb
select 200,0,100,0.4
union all select 200,100,200,0.3
union all select 200,200,300,0.2
union all select 201,0,50,0.6
union all select 201,100,200,0.5
union all select 201,250,300,0.3--要处理的数据信息
declare @编号 varchar(3),@数量 int,@单价 int,@默认费率 decimal(10,2)
declare @结束数量 int,@结果 decimal(38,4)select @编号='200',@数量=350,@单价=1000,@默认费率=1
,@结果=0--计算处理
select @结束数量=case when @数量>end_number then end_number else @数量 end
,@结果=@结果+(@结束数量-start_number)*commision*@单价
from @tb
where product_id=@编号if @结束数量<@数量 set @结果=@结果+(@数量-@结束数量)*@默认费率*@单价--显示计算结果
select 计算结果=@结果
create table 费率表(product_id varchar(3),start_number int,end_number int,commision decimal(20,1))
insert into 费率表
select 200,0,100,0.4
union all select 200,100,200,0.3
union all select 200,200,300,0.2
union all select 201,0,50,0.6
union all select 201,100,200,0.5
union all select 201,250,300,0.3go
--创建计算函数
create function f_calc(
@编号 varchar(3),
@数量 int,
@单价 int,
@默认费率 decimal(10,2)
)
returns decimal(38,4)
as
begin
declare @结束数量 int,@结果 decimal(38,4)
set @结果=0
select @结束数量=case when @数量>end_number then end_number else @数量 end
,@结果=@结果+(@结束数量-start_number)*commision*@单价
from 费率表
where product_id=@编号
if @结束数量<@数量 set @结果=@结果+(@数量-@结束数量)*@默认费率*@单价
return(@结果)
end
go--调用示例
select dbo.f_calc('200',250,1000,1)
,dbo.f_calc('200',350,1000,1)go
--删除测试环境
drop table 费率表
drop function f_calc
create table tbl_commision
(product_id int,start_number int,end_number int,commision numeric(1,1))
insert tbl_commision values (200,0,100,0.4)
insert tbl_commision values (200,100,200,0.3)
insert tbl_commision values (200,200,300,0.2)
insert tbl_commision values (201,0,50,0.6)
insert tbl_commision values (201,100,200,0.5)
insert tbl_commision values (201,250,300,0.3)
------------
declare @je int
declare @commisionType int
declare @sale int
set @commisionType=200
set @sale=278
set @je=0 select @je=@je+(case when start_number>@sale then 0 else
(case when end_number<@sale then end_number-start_number else @sale-start_number end)
end) * commision
from tbl_commision where product_id=@commisionType
print @je
------------
drop table tbl_commision--tbl_salehistory,
create function f_calc(
@编号 varchar(3),
@数量 int,
@单价 int,
@默认费率 decimal(10,2)
)
returns decimal(38,4)
as
begin
declare @结束数量 int,@结果 decimal(38,4)
set @结果=0
select @结束数量=case when @数量>end_number then end_number else @数量 end
,@结果=@结果+(@结束数量-start_number)*commision*@单价
from 费率表
where product_id=@编号 and start_number<@数量 --这里要加多一个判断
if @结束数量<@数量 set @结果=@结果+(@数量-@结束数量)*@默认费率*@单价
return(@结果)
end
go
如:
product_id start_number end_number commision
200 0 100 0.5
200 150 200 0.4
200 200 300 0.3
在这当中,如果@数量=350,因为100-150段没有相应费率,应该启用默认费率。但用楼上的方法是要漏算的。如何解决?
create table 费率表(product_id varchar(3),start_number int,end_number int,commision decimal(20,1))
insert into 费率表
select 200,0,100,0.4
union all select 200,150,200,0.3
union all select 200,200,300,0.2
union all select 201,0,50,0.6
union all select 201,100,200,0.5
union all select 201,250,300,0.3go
--创建计算函数
create function f_calc(
@编号 varchar(3),
@数量 int,
@单价 int,
@默认费率 decimal(10,2)
)
returns decimal(38,4)
as
begin
declare @开始数量 int,@结束数量 int,@结果 decimal(38,4)
select @开始数量=0,@结果=0
select @结束数量=case when @数量>end_number then end_number else @数量 end
,@结果=@结果+case @开始数量 when start_number then 0
else (start_number-@开始数量)*@默认费率*@单价 end
+(@结束数量-start_number)*commision*@单价
,@开始数量=end_number
from 费率表
where product_id=@编号
if @结束数量<@数量 set @结果=@结果+(@数量-@结束数量)*@默认费率*@单价
return(@结果)
end
go--调用示例
select dbo.f_calc('200',250,1,1)
,dbo.f_calc('200',350,1000,1)go
--删除测试环境
drop table 费率表
drop function f_calc
如果一个产品底分段数量不是从小到大排列底话,你这样检索统计回有不同答案。如:
product_id start_number end_number
200 0 100
200 150 200
200 200 300与
200 150 200
200 0 100
200 200 300会有不同结果。请指教!回sunsunup: 业务人员他们要求这样统计,我一个技术实现人员只要满足他们要求就行了。不然人家会说我“狗拿耗子”
create table 费率表(product_id varchar(3),start_number int,end_number int,commision decimal(20,1))
insert into 费率表
select 200,0,100,0.4
union all select 200,150,200,0.3
union all select 200,200,300,0.2
union all select 201,0,50,0.6
union all select 201,100,200,0.5
union all select 201,250,300,0.3go
--创建计算函数
create function f_calc(
@编号 varchar(3),
@数量 int,
@单价 int,
@默认费率 decimal(10,2)
)
returns decimal(38,4)
as
begin
declare @开始数量 int,@结束数量 int,@结果 decimal(38,4)
select @开始数量=0,@结果=0
select @结束数量=case when @数量>end_number then end_number else @数量 end
,@结果=@结果+case @开始数量 when start_number then 0
else (start_number-@开始数量)*@默认费率*@单价 end
+(@结束数量-start_number)*commision*@单价
,@开始数量=end_number
from 费率表
where product_id=@编号
order by start_number --按大小排序就成了.
if @结束数量<@数量 set @结果=@结果+(@数量-@结束数量)*@默认费率*@单价
return(@结果)
end
go--调用示例
select dbo.f_calc('200',250,1,1)
,dbo.f_calc('200',350,1000,1)go
--删除测试环境
drop table 费率表
drop function f_calc