22,207
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[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
(200-100)*1%+(300-200)*2%+(300-300)*3%不是3吗
--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 数量 desc
Update #tb
set tmp= case when id=1 then @count-数量 else (select top 1 数量-#tb.数量 from #tb A where A.id+1 =#tb.id) end
select @compute = sum(tmp*rate) from #tb
drop table #tb
--> 测试数据:[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]
--存储过程
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
end
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(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