- SQL code
create table #tb(FName varchar(10) , FWebPrice varchar(100),FA varchar(100),FB varchar(100) )insert into #tb values('地区A',[email protected]_Pb1','FWebPrice*3','FA+3')insert into #tb values('地区B',[email protected]_Pb2','(@[email protected]_Pb2)/2',[email protected]_Pb1+FA') declare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11select @SMM_Pb2=22 select * from #tb怎么得出计算结果.公式是动态的,所以请问这个的动态语句怎么写？(要考虑到记录数可能很多,公式可能长点,拼接语句的话可能超过长度哦)------解决方案--------------------
你慢慢等人给你拼吧，公式里面还在嵌套字段名称，不是那么一句话的事情
------解决方案--------------------
变态的设计，我有一个办法，你去建临时表，一个字段一个字段的拼接出来往表里面查：
我试了一下，没写完，我也想不到别的办法，看别人吧。
我还是把我的给你贴出来：
- SQL code
create table #tb(FName varchar(10) ,FWebPrice varchar(100),FA varchar(100),FB varchar(100) )insert into #tb values('地区A',[email protected]_Pb1','FWebPrice*3','FA+3')insert into #tb values('地区B',[email protected]_Pb2','(@[email protected]_Pb2)/2',[email protected]_Pb1+FA')--先解决FWebPrice字段create table #test(FName varchar(10) ,FWebPrice int,FA varchar(100),FB varchar(100) )godeclare @str varchar(max)set @str=''select @[email protected]+' when FWebPrice='+QUOTENAME(FWebPrice,'''')+' then '+FWebPrice from #tbset @str='declare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11select @SMM_Pb2=22 insert #test select FName,case [email protected]+' end,FA,FB from #tb'print @strexec(@str)select * from #testgo--创建临时表，以#test为元数据表拼接一下语句，解决FA字段：declare @str2 varchar(max)set @str2=''declare @str3 varchar(max)set @str3=''select @[email protected]+' when FA='+QUOTENAME(FA,'''')+' then '+FA from #testset @str2=',case [email protected]+' end'select @[email protected]+' when FB='+QUOTENAME(FB,'''')+' then '+FB from #testset @str3=',case [email protected]+' end'set @str2='declare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11select @SMM_Pb2=22 select FName,FWebPrice'[email protected][email protected]+' from #test'print @str2exec(@str2)declare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11select @SMM_Pb2=22select FName,FWebPrice,case when FA='FWebPrice*3' then FWebPrice*3 when FA='(@[email protected]_Pb2)/2' then (@[email protected]_Pb2)/2 end,case when FB='FA+3' then FA+3 when [email protected]_Pb1+FA' then @SMM_Pb1+FA end from #test
------解决方案--------------------
- SQL code
------------------听说参数是固定的，那就用了个偷懒的写法，试试吧，能不能满足你的情况。--计算create procedure sp_CalcExpression@Expression varchar(8000),@ decimal(15,2) outasdeclare @sql nvarchar(4000)set @sql = N'set @ = ' + @Expressionexec SP_EXECUTESQL @sql ,N'@ decimal(15,2) out', @ outgo--先解决FWebPrice字段create table #test(FName varchar(10) ,FWebPrice varchar(100),FWebPriceVal int,FA varchar(max),FB varchar(max) )godeclare @str varchar(max)set @str=''select @[email protected]+' when FWebPrice='+QUOTENAME(FWebPrice,'''')+' then '+FWebPrice from #tbset @str='declare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11select @SMM_Pb2=22 insert #test select FName,FWebPrice,FWebPriceVal=case [email protected]+' end, FA,FB from #tb'exec(@str)godeclare @SMM_Pb1 numeric(15,2) declare @SMM_Pb2 numeric(15,2) select @SMM_Pb1=11,@SMM_Pb2=22 declare mycursor cursorforselect FName,FWebPriceVal, FA=replace(replace(replace(FA,[email protected]_Pb1',@SMM_Pb1),[email protected]_Pb2',@SMM_Pb2),'FWebPrice','('+cast(FWebPriceVal as varchar(10))+')'), FB=replace(replace(replace(replace(FB,[email protected]_Pb1',@SMM_Pb1),[email protected]_Pb2',@SMM_Pb2),'FWebPrice','('+cast(FWebPriceVal as varchar(10))+')'), 'FA','('+replace(replace(replace(FA,[email protected]_Pb1',@SMM_Pb1),[email protected]_Pb2',@SMM_Pb2),'FWebPrice','('+cast(FWebPriceVal as varchar(10))+')')+')')from #testopen mycursorselect FName,FWebPrice,FA,FB into #tmp from #test where 1<>1declare @FName varchar(10),@FWebPriceVal int,@FA varchar(max),@FB varchar(max)fetch next from mycursor into @FName,@FWebPriceVal,@FA,@FBwhile @@fetch_status=0begin declare @FAVal numeric(15,2),@FBVal numeric(15,2) declare @x decimal(15,2) exec sp_CalcExpression @FA, @x out set @[email protected] exec sp_CalcExpression @FB, @x out set @[email protected] insert #tmp select @FName,@FWebPriceVal,@FAVal,@FBVal fetch next from mycursor into @FName,@FWebPriceVal,@FA,@FBendclose mycursordeallocate mycursorselect * from #tmp====================================================================FName FWebPrice FA FB ---------- ---------------- -------------------- ----------地区A 11 33.00 36.00 地区B 22 16.50 27.50