计算公式

[kx_long]

表cal_value_t:
code col_value flag
c001 1000 N
c002 1234 N
c003 768 N
c004 879 N
C005 需计算 Y
COO6 需计算 Y

表cal_formula_t ：

code formula
C005 ((c001+c002)*90+10000)/90
C006 C005+coo1+c002*7

说明：表cal_value_t定义的记录是每个编码对应一个数值，但是标识位flag==Y 时，表示COO5 和COO6 需要通过从表cal_formula_t 取计算公式计算，公式中的数据项从表cal_value_t中取，如解析后
C005=（（1000+1234）*90+10000）/90。

[usher_gml]

[Quote=引用 2 楼 chuifengde 的回复:]
SQL codeSET NOCOUNT ON

CREATE TABLE ls(code CHAR(4),col_value FLOAT, flag CHAR(1))
INSERT ls SELECT 'c001', 1000, 'N'
union all select 'c002', 1234, 'N'
union all select 'c003', 768, 'N'
union all select 'c004', 879, 'N'
union all select 'C005', NULL, 'Y'
union all select 'C006', null, 'Y'

CREATE TABLE lsa(code CHAR(4),formula VARCHAR(100))
I…
[/Quote]
厉害...

[feixianxxx]

[Quote=引用 2 楼 chuifengde 的回复:]
SQL codeSET NOCOUNT ON

CREATE TABLE ls(code CHAR(4),col_value FLOAT, flag CHAR(1))
INSERT ls SELECT 'c001', 1000, 'N'
union all select 'c002', 1234, 'N'
union all select 'c003', 768, 'N'
union all select 'c004', 879, 'N'
union all select 'C005', NULL, 'Y'
union all select 'C006', null, 'Y'

CREATE TABLE lsa(code CHAR(4),formula VARCHAR(100))
I…
[/Quote]

服！

[美到心痛]

假设楼主的字段属性collation已经含有case sensitive.即区分大小写。如果不是可用alter修改为。
假设楼主是想把解析后的公式更新原公式。

declare @icount int
select @icount=count(1) from cal_formula_t where formula like '%[cC]%' 


while @icount>0
begin
	
	update cal_formula_t set formula=replace(formula,substring(formula,charindex('C',formula),4),(select a.formula  from cal_formula_t as a where a.code=substring(cal_formula_t.formula,charindex('C',cal_formula_t.formula),4) and a.code<>cal_formula_t.code ))
	where formula like '%C%'
	update cal_formula_t set formula=replace(formula,substring(formula,charindex('c',formula),4),(select a.col_value from cal_value_t as a where a.code=substring(cal_formula_t.formula,charindex('c',cal_formula_t.formula),4)))
	where formula like '%c%'
	select @icount=count(1) from cal_formula_t where formula like '%[cC]%' 
end

[chuifengde]

SET NOCOUNT ON 

CREATE TABLE ls(code CHAR(4),col_value FLOAT,    flag CHAR(1))
INSERT ls SELECT 'c001',      1000, 'N' 
union all select 'c002',      1234, 'N' 
union all select 'c003',      768,  'N' 
union all select 'c004',      879,  'N' 
union all select 'C005',      NULL, 'Y'
union all select 'C006',      null, 'Y' 

CREATE TABLE lsa(code CHAR(4),formula VARCHAR(100))
INSERT lsa SELECT 'C005','((c001+c002)*90+10000)/90' 
UNION ALL SELECT 'C006','C005+C001+c002*7' 

DECLARE @sql VARCHAR(1000)
SELECT @sql=ISNULL(@sql+',','')+'max(case when code='''+code +''' then col_value else 0 end) ['+code +']' FROM ls l
EXEC('select '+@sql+' into lsb from ls')

exec MASTER..xp_execresultset 'SELECT ''update lsb set ''+code+''=''+formula FROM lsa ',N'库名'
exec MASTER..xp_execresultset '
SELECT ''UPDATE a SET col_value=''+REPLACE(formula,''c'',''b.c'')+'' FROM ls a,lsb b where code=''''''+code+'''''''' from lsa a 
',N'库名'

SELECT * FROM ls
go
DROP TABLE  ls,lsa,lsb

--result
/*code col_value                                             flag 
---- ----------------------------------------------------- ---- 
c001 1000.0                                                N
c002 1234.0                                                N
c003 768.0                                                 N
c004 879.0                                                 N
C005 2345.1111111111113                                    Y
C006 11983.111111111111                                    Y
*/

[-晴天]

--你这两个公式根本没有共性或者规律,因此谈不上什么公式,只是个计算式而已.
--最简单的方法:
update cal_value_t set col_value=(((select sum(col_value) from cal_value_t where code='c001' or code='c002')*90+100000)/90 where code='c005'
go
update cal_value_t set col_value=((select sum(col_value) from cal_value_t where colde='c002' or code='c001)+
(select col_value) from cal_value_t where colde='c002')*7)
where code='c006'

[-晴天]

update cal_value_t set col_value=((select sum(col_value) from cal_value_t where colde='c002' or code='c001')+
(select col_value) from cal_value_t where colde='c002')*7)
where code='c006'

[feixianxxx]

update cal_value_t
set col_value=formula
from cal_fofmula_t f,cal_value_t v
where v.code=f.code and v.flag='Y'

[feixianxxx]

update cal_value_t
set col_value=formula
from cal_fofmula_t f,cal_value_t v
where v.code=f.code and v.flag='Y'