--创建函数 create function BLC(@bxbl varchar(50),@money int) returns numeric(8,2) as begin --100(500:0.5)(2000:0.3)0.6 declare @first int --起付线 相当于100 declare @bl varchar(50) --单个的报销比例 相当于500:0.5 declare @end int ---')'所在位置 declare @je int --金额 相当于500 declare @xs numeric(8,2) --比例 相当于0.5 declare @bx int --可以报销金额 set @first=cast(substring(@bxbl,1,charindex('(',@bxbl)-1) as int) set @money=@money-@first set @bx=0 while(charindex('(',@bxbl)>0 and @money>0) begin set @end = charindex(')',@bxbl) set @bl = substring(@bxbl,charindex('(',@bxbl)+1,charindex(')',@bxbl)-charindex('(',@bxbl)-1) set @je = cast(substring(@bl,1,charindex(':',@bl)-1) as int) set @xs = cast(substring(@bl,charindex(':',@bl)+1,len(@bl)) as numeric(8,2)) if(@money<=@je) begin set @bx=@bx + @money*@xs end else begin set @bx=@bx + @je*@xs end
set @money=@money-@je
set @bxbl=substring(@bxbl,@end+1,len(@bxbl)) end if(isnull(@bxbl,'')<>'' and @money>0) begin set @bx=@bx+@money*cast(@bxbl as numeric(8,2)) end return @bx end go --测试 select dbo.blc('100(500:0.5)(2000:0.3)0.6 ',5000) /*结果: 2290.00 */
create function BLC(@bxbl varchar(50),@money int)
returns numeric(8,2)
as
begin --100(500:0.5)(2000:0.3)0.6
declare @first int --起付线 相当于100
declare @bl varchar(50) --单个的报销比例 相当于500:0.5
declare @end int ---')'所在位置
declare @je int --金额 相当于500
declare @xs numeric(8,2) --比例 相当于0.5
declare @bx int --可以报销金额
set @first=cast(substring(@bxbl,1,charindex('(',@bxbl)-1) as int)
set @money=@money-@first
set @bx=0
while(charindex('(',@bxbl)>0 and @money>0)
begin
set @end = charindex(')',@bxbl)
set @bl = substring(@bxbl,charindex('(',@bxbl)+1,charindex(')',@bxbl)-charindex('(',@bxbl)-1)
set @je = cast(substring(@bl,1,charindex(':',@bl)-1) as int)
set @xs = cast(substring(@bl,charindex(':',@bl)+1,len(@bl)) as numeric(8,2))
if(@money<=@je)
begin
set @bx=@bx + @money*@xs
end
else
begin
set @bx=@bx + @je*@xs
end
set @money=@money-@je
set @bxbl=substring(@bxbl,@end+1,len(@bxbl))
end
if(isnull(@bxbl,'')<>'' and @money>0)
begin
set @bx=@bx+@money*cast(@bxbl as numeric(8,2))
end
return @bx
end
go
--测试
select dbo.blc('100(500:0.5)(2000:0.3)0.6 ',5000) /*结果:
2290.00
*/
returns money
as
begin
declare @1 money, @2 money, @3 money, @4 money, @5 money, @6 money
select @1 = left(@fun, charindex('(',@fun)-1), @fun = stuff(@fun, 1, charindex('(',@fun), '')
select @2 = left (@fun, charindex(':',@fun)-1), @fun = stuff(@fun, 1, charindex(':',@fun), '')
select @3 = left (@fun, charindex(')',@fun)-1), @fun = stuff(@fun, 1, charindex('(',@fun), '')
select @4 = left (@fun, charindex(':',@fun)-1), @fun = stuff(@fun, 1, charindex(':',@fun), '')
select @5 = left (@fun, charindex(')',@fun)-1), @6 = stuff(@fun, 1, charindex(')',@fun), '')
return (@2*@3 + @4*@5 + (@var-@1-@2-@4)*0.6)
end
go--调用
select dbo.fn_calc('100(500:0.5)(2000:0.3)0.6', 5000) --2290.00