鍵億建公式表如下
itemid formula Param1 Param2
1003 (Param1 +Param12)/2 1001 1002插入記錄可以直接插入1001,1002,和公式
這樣就可以動態計算了大致算法如下,自己可以嘗試寫寫select @formula=formula from F where itemid=@itemidselect @formula=replace(@formula,‘Param1’,cast(t.Amount as varchar(10))
from F inner join t on F.Param1=t.itemid
where f.itemid=@itemidselect @formula=replace(@formula,‘Param2’,cast(t.Amount as varchar(10))
from F inner join t on F.Param2=t.itemid
where f.itemid=@itemidselect @sql='update t
set amount ='+@formula
+'where t.Itemid='''+@itemid+''''
exec @sql
itemid formula Param1 Param2
1003 (Param1 +Param12)/2 1001 1002插入記錄可以直接插入1001,1002,和公式
這樣就可以動態計算了大致算法如下,自己可以嘗試寫寫select @formula=formula from F where itemid=@itemidselect @formula=replace(@formula,‘Param1’,cast(t.Amount as varchar(10))
from F inner join t on F.Param1=t.itemid
where f.itemid=@itemidselect @formula=replace(@formula,‘Param2’,cast(t.Amount as varchar(10))
from F inner join t on F.Param2=t.itemid
where f.itemid=@itemidselect @sql='update t
set amount ='+@formula
+'where t.Itemid='''+@itemid+''''
exec @sql
insert into tb_formula
select '1003','[1001]+[1002]'
godeclare @code varchar(30),@sql varchar(8000)
declare tc cursor for select formula from tb_formulaopen tcfetch next from tc into @codewhile @@fetch_status=0
begin
set @sql=''
select @sql=@sql+',['+itemid+']=max(case itemid when '''+itemid+''' then planamount end)' from tb_plan where compcode='07750' and period='2008'
select @sql
set @sql='select '+@code+' from (select '+stuff(@sql,1,1,'')+' from tb_plan where compcode=''04770'' and period=''2008'' ) t'
select @sql
exec(@sql)
fetch next from tc into @code
endclose tc
deallocate tc
begin
set @sql=''
select @sql=@sql+',['+itemid+']=max(case itemid when '''+itemid+''' then planamount end)' from tb_plan where compcode='04770' and period='2008'
select @sql
set @sql='select '+@code+' from (select '+stuff(@sql,1,1,'')+' from tb_plan where compcode=''04770'' and period=''2008'' ) t'
select @sql
exec(@sql)
fetch next from tc into @code
endclose tc
deallocate tc
CREATE PROCEDURE BMS_BUSINESS_UPDATEBYFORMULA
(
@COMPCODE NVARCHAR(5)
,@PERIOD NVARCHAR(4)
)
AS
BEGIN
SET NOCOUNT ON;
declare @code varchar(300),@sql varchar(8000),@fid varchar(300)
declare tc cursor for select fid from tb_formula
open tc
fetch next from tc into @fid
while @@fetch_status=0
begin
select @code=formula from tb_formula where fid=@fid
set @sql=''
select @sql=@sql+',['+itemid+']=max(case itemid when '''+itemid+''' then planamount end)' from tb_plan where compcode=@COMPCODE and period=@PERIOD
set @sql='update tb_plan set planamount=(select '+@code+' from (select '+stuff(@sql,1,1,'')+' from tb_plan where compcode='''+@COMPCODE+''' and period='''+@PERIOD+''' ) t)'
set @sql=@sql+'where compcode='''+@COMPCODE+''' and period='''+@PERIOD+''' and itemid='''+@fid+''''
exec(@sql)
fetch next from tc into @fid
endclose tc
deallocate tc
END
另外,这个代码应该还可以优化一下,因为@SQL(8000)如果总表中的科目很
多时候,会可能溢出,可以再缩小搜寻的范围.明天开专题会应该可以塞住用
户嘴巴了.
select '2005','[1001]+[1002]+[1004]'