表结构:
数量 比例
100 1%
200 2%
300 3%
。参数:
@数量 decimal
@标志 int
要求输入一个数量分段点和一个计算方法标志(两种类型),每个数量分段值都不固定,然后返回对应计算值,如输入(350,1)则返回350*3%=10.5,而输入(350,2)则返回 (200-100)*1%+(300-200)*2%+(350-300)*3%,也就是说当输入值达到表中某一数量时,如果标志为1则按一个比例计算,而标志为2时则要计算每个分段值,谢谢!--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([数量] int,[比例] varchar(2))
go
insert [tb]
select 100,'1%' union all
select 200,'2%' union all
select 300,'3%'select * from [tb]--存储过程
create proc proc_trans_compute @count decimal(18,2),@flag int,@compute decimal(18,2) output
as
declare @total decimal(18,2)
if @flag = 1
begin
select @compute = @count*(select top 1 cast(replace(比例,'%','') as int)*0.01
from [tb] where [数量]<=@count order by [数量] desc)end
else if @flag = 2
begin
select @total=sum([数量]),
@compute=sum([数量]*cast(replace(比例,'%','') as int)*0.01)
from [tb] t
where (select sum([数量]) from [tb] where [数量]<=t.[数量])<=@count
if @total < @count
begin
select @compute=@compute+(@count-@total)*(select top 1 cast(replace(比例,'%','') as int)*0.01
from [tb] where [数量]<=@count order by [数量] desc)
end
end--调用
declare @compute decimal(18,2)
exec proc_trans_compute 350,2,@compute output
select @compute
------------------
6.5前几天问了这个问题,后来发现这个存储过程计算结果不对,结果偏大应该是多算了,请帮忙修改一下或者提供新的,本人sql循环不熟悉又时间紧迫,谢谢了!
数量 比例
100 1%
200 2%
300 3%
。参数:
@数量 decimal
@标志 int
要求输入一个数量分段点和一个计算方法标志(两种类型),每个数量分段值都不固定,然后返回对应计算值,如输入(350,1)则返回350*3%=10.5,而输入(350,2)则返回 (200-100)*1%+(300-200)*2%+(350-300)*3%,也就是说当输入值达到表中某一数量时,如果标志为1则按一个比例计算,而标志为2时则要计算每个分段值,谢谢!--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([数量] int,[比例] varchar(2))
go
insert [tb]
select 100,'1%' union all
select 200,'2%' union all
select 300,'3%'select * from [tb]--存储过程
create proc proc_trans_compute @count decimal(18,2),@flag int,@compute decimal(18,2) output
as
declare @total decimal(18,2)
if @flag = 1
begin
select @compute = @count*(select top 1 cast(replace(比例,'%','') as int)*0.01
from [tb] where [数量]<=@count order by [数量] desc)end
else if @flag = 2
begin
select @total=sum([数量]),
@compute=sum([数量]*cast(replace(比例,'%','') as int)*0.01)
from [tb] t
where (select sum([数量]) from [tb] where [数量]<=t.[数量])<=@count
if @total < @count
begin
select @compute=@compute+(@count-@total)*(select top 1 cast(replace(比例,'%','') as int)*0.01
from [tb] where [数量]<=@count order by [数量] desc)
end
end--调用
declare @compute decimal(18,2)
exec proc_trans_compute 350,2,@compute output
select @compute
------------------
6.5前几天问了这个问题,后来发现这个存储过程计算结果不对,结果偏大应该是多算了,请帮忙修改一下或者提供新的,本人sql循环不熟悉又时间紧迫,谢谢了!
if object_id('[tb]') is not null drop table [tb]
create table [tb]([数量] int,[比例] varchar(2))
go
insert [tb]
select 100,'1%' union all
select 200,'2%' union all
select 300,'3%'select * from [tb]--存储过程
alter proc proc_trans_compute @count decimal(18,2),@flag int,@compute decimal(18,2) output
as
if @count < (select min([数量]) from [tb])
begin
select @compute = 0
return
enddeclare @total decimal(18,2)
if @flag = 1
begin
select @compute = @count*(select top 1 cast(replace(比例,'%','') as int)*0.01
from [tb] where [数量]<=@count order by [数量] desc)end
else if @flag = 2
begin
select @total=sum([数量]),
@compute= sum(total)
from
(
select [数量],
((select top 1 [数量] from [tb] where [数量]> t.[数量] order by [数量])-[数量])*cast(replace(比例,'%','') as int)*0.01 as total
from [tb] t
where (select sum([数量]) from [tb] where [数量]<=t.[数量])<=@count
) r
if @total < @count
begin
select @compute=@compute+(@count-@total)*(select top 1 cast(replace(比例,'%','') as int)*0.01
from [tb] where [数量]<=@count order by [数量] desc)
end
end--调用
declare @compute decimal(18,2)
exec proc_trans_compute 350,2,@compute output
select @compute---------------------
4.50
--flag=2时试试这个select id=identity(int,1,1), [数量] , rate=cast(replace([比例],'%','') as int)*0.01,tmp=0
into #tb
from tb as A
where 数量<=@count
order by 数量 descUpdate #tb
set tmp= case when id=1 then @count-数量 else (select top 1 数量-#tb.数量 from #tb A where A.id+1 =#tb.id) endselect @compute = sum(tmp*rate) from #tbdrop table #tb
playwarcraft:这种方式好像不行,我的id是自增长的,如果删掉再添加就不一定连续
playwarcraft:这里面的A.id应该是表里的吧,from #tb A where A.id+1 =#tb.id