求关于公式替换的SQL语句

[tommychim]

TABLE A --记录每行每组的值
LINEID GROUP VALUE
--------------------------
0001 A01 20
0001 A02 30
0001 A03 40
0002 A01 10
0002 A03 15
0003 A02 25

TABLE B --公式定义内容
LINEID CALOP
--------------------------
0004 0001+0002-0003

求根据TABLE B的公式(可能是+,-,*,/,但同一公式里只有同级的操作),计算出该行的值,插入到TABLE A中,如下:
LINEID GROUP VALUE
--------------------------
0001 A01 20
0001 A02 30
0001 A03 40
0002 A01 10
0002 A03 15
0003 A02 25
0004 A01 30(新20+10-0)
0004 A02 5(新30+0-25)
0004 A03 55(新40+15-0)

[sgucxc0]

insert into A select [0004]=[0001]+[0002]-[0003] from A group by [GROUP]

--验证结果
select * from A

--我没测试,LZ自己试试看,对不

[sgucxc0]

写得太快了,上面写错了

[子陌红尘]

create TABLE A(LINEID varchar(6),[GROUP] varchar(6),VALUE int)
insert into A select '0001','A01',20
insert into A select '0001','A02',30
insert into A select '0001','A03',40
insert into A select '0002','A01',10
insert into A select '0002','A03',15
insert into A select '0003','A02',25

create TABLE B(LINEID varchar(6),CALOP varchar(20))
insert into B select '0004','0001+0002-0003'
go

declare @LINEID varchar(6),@STR varchar(8000)
select @LINEID=LINEID,@STR=CALOP from B

select @STR=replace(@STR,LINEID,'isnull((select VALUE from A where LINEID='''+LINEID+''' and [GROUP]=t.[GROUP]),0)')
from (select distinct LINEID from A) t where charindex(LINEID,@STR)>0

set @STR='insert into A select '''+@LINEID+''',[GROUP],'+@STR+' from A t group by [GROUP]'

exec(@STR)

select * from A
/*
LINEID GROUP VALUE
------ ------ -----------
0001 A01 20
0001 A02 30
0001 A03 40
0002 A01 10
0002 A03 15
0003 A02 25
0004 A01 30
0004 A02 5
0004 A03 55
*/
GO


drop table A,B
GO

[子陌红尘]

如果B表不止一条记录，可以借助游标来实现。

[tommychim]

运行结果正确,再研究一下先,一会结帖

[子陌红尘]

使用游标的例子：

create TABLE A(LINEID varchar(6),[GROUP] varchar(6),VALUE int)
create TABLE B(LINEID varchar(6),CALOP varchar(20))
insert into A select '0001','A01',20
insert into A select '0001','A02',30
insert into A select '0001','A03',40
insert into A select '0002','A01',10
insert into A select '0002','A03',15
insert into A select '0003','A02',25
insert into B select '0004','0001+0002-0003'
insert into B select '0005','0001-0003'
go

declare @LINEID varchar(6),@STR varchar(8000)

declare tc cursor for select LINEID,CALOP from B

open tc

fetch next from tc into @LINEID,@STR

while @@fetch_status=0
begin
select @STR=replace(@STR,LINEID,'isnull((select VALUE from A where LINEID='''+LINEID+''' and [GROUP]=t.[GROUP]),0)')
from (select distinct LINEID from A) t where charindex(LINEID,@STR)>0

set @STR='insert into A select '''+@LINEID+''',[GROUP],'+@STR+' from A t group by [GROUP]'

exec(@STR)

fetch next from tc into @LINEID,@STR
end

close tc
deallocate tc

select * from A
/*
LINEID GROUP VALUE
------ ------ -----------
0001 A01 20
0001 A02 30
0001 A03 40
0002 A01 10
0002 A03 15
0003 A02 25
0004 A01 30
0004 A02 5
0004 A03 55
0005 A01 20
0005 A02 5
0005 A03 40
*/
GO


drop table A,B
GO

[tommychim]

再问一个问题,如果TABLE A有两个VALUE的字段VALUE1,VALUE2,公式是一样的,那么怎么写呢?