表:TableTest字段: GPrice Fomula
500 Round(GPrice*41/31,2)
770 Round(GPrice*61/31,2)
... ... 其中Fomula是fomula字段,有可能为空或null,我现在就想把这个字段转换成真正可以计算的公式,把GPrice代入到Fomula中,计算出结果,帮忙想解决方法。
500 Round(GPrice*41/31,2)
770 Round(GPrice*61/31,2)
... ... 其中Fomula是fomula字段,有可能为空或null,我现在就想把这个字段转换成真正可以计算的公式,把GPrice代入到Fomula中,计算出结果,帮忙想解决方法。
open _cursor
declare @price sysname,@fomula sysname,@sql varchar(2000)
set @sql=''
FETCH NEXT FROM _cursor INTO @price,@fomula
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
set @sql=@sql+' select '+replace(@fomula,'Gprice',rtrim(convert(char(5),@price)))
end
FETCH NEXT FROM _cursor INTO @price,@fomula
end
exec(@sql)大致这样 还要多些判断
/*以下可以直接用*/
declare @centers nvarchar(2000)
declare @sql varchar(2000)
set @centers='17462.72/22.88*35.05+(0.0+17265.60)+29149.12';
select @centers --结果:17462.72/22.88*35.05+(0.0+17265.60)+29149.12
set @sql='SELECT ROUND('+@centers+', 2)'
exec(@sql)--结果:73165.960000000
你也可以看看这个!
btw:
我回复很快吗?
竟然提示我:
回复太快!如果你是恶意刷楼,将会受到严厉惩罚!
begindeclare @fReturn float
declare @sNewFomula varchar(50)
declare @K float,@A float,@P intset @fReturn=0set @sNewFomula=replace(replace(upper(replace(@Fomula,' ','')),'ROUND(GPRICE*',''),')','')if(isnull(@sNewFomula,'')='')return @fReturnif(isnull(@GPrice,0)=0)return @fReturnset @K=cast(left(@sNewFomula,charindex('/',@sNewFomula)-1) as float)set @sNewFomula=right(@sNewFomula,len(@sNewFomula)-charindex('/',@sNewFomula))set @A=cast(left(@sNewFomula,charindex(',',@sNewFomula)-1) as float)set @P=cast(right(@sNewFomula,1) as float)set @fReturn=round(@GPrice*@K/@A,@P)return @fReturn
endgoselect GPrice,Fomula,dbo.DealwithFomula(Fomula,GPrice) from TableTestdrop function dbo.DealwithFomulago
这是我写的