--> liangCK小梁 于2008-11-03 --> 生成测试数据: @T DECLARE @T TABLE (A INT,OP VARCHAR(1),B INT,C SQL_VARIANT) INSERT INTO @T SELECT 2,'*',25,null UNION ALL SELECT 2,'+',3,null UNION ALL SELECT 2,'-',1,null UNION ALL SELECT 2,'/',1,null--SQL查询如下:SELECT A,OP,B, C=CASE WHEN OP='*' THEN A*B WHEN OP='+' THEN A+B WHEN OP='-' THEN A-B WHEN OP='/' THEN A/B ELSE 0 END FROM @T/* A OP B C ----------- ---- ----------- ----------- 2 * 25 50 2 + 3 5 2 - 1 1 2 / 1 2(4 行受影响)*/
--> liangCK小梁 于2008-11-03 --> 生成测试数据: @T DECLARE @T TABLE (A INT,OP VARCHAR(1),B INT,C DECIMAL(9,2)) INSERT INTO @T SELECT 2,'*',25,null UNION ALL SELECT 2,'+',3,null UNION ALL SELECT 2,'-',1,null UNION ALL SELECT 2,'/',1,null--SQL查询如下:UPDATE @T SET C=CASE WHEN OP='*' THEN A*B WHEN OP='+' THEN A+B WHEN OP='-' THEN A-B WHEN OP='/' THEN A/B ELSE 0 ENDSELECT * FROM @T/* A OP B C ----------- ---- ----------- --------------------------------------- 2 * 25 50.00 2 + 3 5.00 2 - 1 1.00 2 / 1 2.00(4 行受影响)*/
if object_id('[tb]') is not null drop table [tb] create table [tb](id int identity(1,1),[a] int,[op] varchar(1),[b] int,[c] int) insert [tb] select 2,'*',25,null union all select 2,'+',3,null union all select 2,'-',1,null union all select 2,'/',1,null declare @i int,@count int,@sql nvarchar(2000),@val int set @i=1 select @count=max(id) from tb while @i<=@count begin select @sql='select @val='+rtrim(a)+op+rtrim(b) from tb where id=@i exec sp_executesql @sql,N'@val int output',@val output update tb set c=@val where id=@i set @i=@i+1 endselect * from [tb] --测试结果: /* id a op b c ----------- ----------- ---- ----------- ----------- 1 2 * 25 50 2 2 + 3 5 3 2 - 1 1 4 2 / 1 2(4 row(s) affected)*/
小梁的方法是最简单的,你还想要什么方法?另外一个方法就是把a op b三列拼接成算数表达式,并且是除的话,检查下b不为0
简化版: if object_id('[tb]') is not null drop table [tb] create table [tb](id int identity(1,1),[a] int,[op] varchar(1),[b] int,[c] int) insert [tb] select 2,'*',25,null union all select 2,'+',3,null union all select 2,'-',1,null union all select 2,'/',1,null declare @sql nvarchar(max) select @sql=isnull(@sql+';','')+'update tb set c='+rtrim(a)+op+rtrim(b)+' where id='+rtrim(id) from tb exec(@sql)select * from [tb] --测试结果: /* id a op b c ----------- ----------- ---- ----------- ----------- 1 2 * 25 50 2 2 + 3 5 3 2 - 1 1 4 2 / 1 2(4 row(s) affected)*/
字符不多用case when ..判断;想代码简单可以写一下函数计算
use tempdb goEXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGURE WITH OVERRIDE GO create function f_calc( @str varchar(1000) --要计算的表达式 )returns sql_variant as begin declare @re sql_variant declare @err int,@src varchar(255),@desc varchar(255) declare @obj int exec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj out if @err<>0 goto lb_err exec @err=sp_oasetproperty @obj,'Language','vbscript' if @err<>0 goto lb_err exec @err=sp_oamethod @obj,'Eval',@re out,@str if @err=0 return(@re) lb_err: exec sp_oageterrorinfo NULL, @src out, @desc out declare @errb varbinary(4),@s varchar(20) set @errb=cast(@err as varbinary(4)) exec master..xp_varbintohexstr @errb,@s out return('错误号: '+@s+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@desc) end goDECLARE @T TABLE (A INT,OP VARCHAR(1),B INT,C SQL_VARIANT) INSERT INTO @T SELECT 2,'*',25,null UNION ALL SELECT 2,'+',3,null UNION ALL SELECT 2,'-',1,null UNION ALL SELECT 2,'/',1,nullupdate @t SET C = dbo.f_calc(convert(varchar(10),A)+OP+convert(varchar(10),B))select * from @T DROP FUNCTION f_calc GO/** 2 * 25 50 2 + 3 5 2 - 1 1 2 / 1 2 **/ 表达式求值
2005修正如下: create function f_calc( @str varchar(1000)--要计算的表达式 )returns sql_variant as begin declare @re sql_variantdeclare @err int,@src varchar(255),@desc varchar(255) declare @obj intexec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj out if @err<>0 goto lb_errexec @err=sp_oasetproperty @obj,'Language','vbscript' if @err<>0 goto lb_errexec @err=sp_oamethod @obj,'Eval',@re out,@str if @err=0 return(@re)lb_err: exec sp_oageterrorinfo NULL, @src out, @desc out declare @errb varbinary(4),@s varchar(20) set @errb=cast(@err as varbinary(4)) exec master..sys.fn_varbintohexstr @errb,@s out return('错误号: '+@s+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@desc) end go create TABLE ta(A INT,OP VARCHAR(1),B INT,C DECIMAL(9,2)) INSERT INTO ta SELECT 2,'*',25,null UNION ALL SELECT 2,'+',3,null UNION ALL SELECT 2,'-',1,null UNION ALL SELECT 2,'/',1,null ; update ta set c =cast( dbo.f_calc(ltrim(a)+op+ltrim(b)) as DECIMAL(9,2))select * from tadrop table ta drop function f_calc /* A OP B C ----------- ---- ----------- --------------------------------------- 2 * 25 50.00 2 + 3 5.00 2 - 1 1.00 2 / 1 2.00(4 行受影响) */ exec master..sys.fn_varbintohexstr @errb,@s out
--设表名为tt update tt set c=case op when '*' then a*b when '+' then a+b when '-' then a-b when '/' then a/b else 0 end
--> 生成测试数据: @T
DECLARE @T TABLE (A INT,OP VARCHAR(1),B INT,C SQL_VARIANT)
INSERT INTO @T
SELECT 2,'*',25,null UNION ALL
SELECT 2,'+',3,null UNION ALL
SELECT 2,'-',1,null UNION ALL
SELECT 2,'/',1,null--SQL查询如下:SELECT A,OP,B,
C=CASE WHEN OP='*' THEN A*B
WHEN OP='+' THEN A+B
WHEN OP='-' THEN A-B
WHEN OP='/' THEN A/B
ELSE 0
END
FROM @T/*
A OP B C
----------- ---- ----------- -----------
2 * 25 50
2 + 3 5
2 - 1 1
2 / 1 2(4 行受影响)*/
--> 生成测试数据: @T
DECLARE @T TABLE (A INT,OP VARCHAR(1),B INT,C DECIMAL(9,2))
INSERT INTO @T
SELECT 2,'*',25,null UNION ALL
SELECT 2,'+',3,null UNION ALL
SELECT 2,'-',1,null UNION ALL
SELECT 2,'/',1,null--SQL查询如下:UPDATE @T
SET C=CASE WHEN OP='*' THEN A*B
WHEN OP='+' THEN A+B
WHEN OP='-' THEN A-B
WHEN OP='/' THEN A/B
ELSE 0
ENDSELECT * FROM @T/*
A OP B C
----------- ---- ----------- ---------------------------------------
2 * 25 50.00
2 + 3 5.00
2 - 1 1.00
2 / 1 2.00(4 行受影响)*/
if object_id('[tb]') is not null drop table [tb]
create table [tb](id int identity(1,1),[a] int,[op] varchar(1),[b] int,[c] int)
insert [tb]
select 2,'*',25,null union all
select 2,'+',3,null union all
select 2,'-',1,null union all
select 2,'/',1,null
declare @i int,@count int,@sql nvarchar(2000),@val int
set @i=1
select @count=max(id) from tb
while @i<=@count
begin
select @sql='select @val='+rtrim(a)+op+rtrim(b) from tb where id=@i
exec sp_executesql @sql,N'@val int output',@val output
update tb set c=@val where id=@i
set @i=@i+1
endselect * from [tb]
--测试结果:
/*
id a op b c
----------- ----------- ---- ----------- -----------
1 2 * 25 50
2 2 + 3 5
3 2 - 1 1
4 2 / 1 2(4 row(s) affected)*/
if object_id('[tb]') is not null drop table [tb]
create table [tb](id int identity(1,1),[a] int,[op] varchar(1),[b] int,[c] int)
insert [tb]
select 2,'*',25,null union all
select 2,'+',3,null union all
select 2,'-',1,null union all
select 2,'/',1,null
declare @sql nvarchar(max)
select @sql=isnull(@sql+';','')+'update tb set c='+rtrim(a)+op+rtrim(b)+' where id='+rtrim(id) from tb
exec(@sql)select * from [tb]
--测试结果:
/*
id a op b c
----------- ----------- ---- ----------- -----------
1 2 * 25 50
2 2 + 3 5
3 2 - 1 1
4 2 / 1 2(4 row(s) affected)*/
goEXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE WITH OVERRIDE
GO
create function f_calc(
@str varchar(1000) --要计算的表达式
)returns sql_variant
as
begin declare @re sql_variant
declare @err int,@src varchar(255),@desc varchar(255)
declare @obj int
exec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj out
if @err<>0 goto lb_err
exec @err=sp_oasetproperty @obj,'Language','vbscript'
if @err<>0 goto lb_err
exec @err=sp_oamethod @obj,'Eval',@re out,@str
if @err=0 return(@re)
lb_err:
exec sp_oageterrorinfo NULL, @src out, @desc out
declare @errb varbinary(4),@s varchar(20)
set @errb=cast(@err as varbinary(4))
exec master..xp_varbintohexstr @errb,@s out
return('错误号: '+@s+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@desc)
end
goDECLARE @T TABLE (A INT,OP VARCHAR(1),B INT,C SQL_VARIANT)
INSERT INTO @T
SELECT 2,'*',25,null UNION ALL
SELECT 2,'+',3,null UNION ALL
SELECT 2,'-',1,null UNION ALL
SELECT 2,'/',1,nullupdate @t
SET C = dbo.f_calc(convert(varchar(10),A)+OP+convert(varchar(10),B))select * from @T
DROP FUNCTION f_calc
GO/**
2 * 25 50
2 + 3 5
2 - 1 1
2 / 1 2
**/
表达式求值
create function f_calc(
@str varchar(1000)--要计算的表达式
)returns sql_variant
as
begin
declare @re sql_variantdeclare @err int,@src varchar(255),@desc varchar(255)
declare @obj intexec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj out
if @err<>0 goto lb_errexec @err=sp_oasetproperty @obj,'Language','vbscript'
if @err<>0 goto lb_errexec @err=sp_oamethod @obj,'Eval',@re out,@str
if @err=0 return(@re)lb_err:
exec sp_oageterrorinfo NULL, @src out, @desc out
declare @errb varbinary(4),@s varchar(20)
set @errb=cast(@err as varbinary(4))
exec master..sys.fn_varbintohexstr @errb,@s out
return('错误号: '+@s+char(13)+'错误源: '+@src+char(13)+'错误描述: '+@desc)
end
go
create TABLE ta(A INT,OP VARCHAR(1),B INT,C DECIMAL(9,2))
INSERT INTO ta
SELECT 2,'*',25,null UNION ALL
SELECT 2,'+',3,null UNION ALL
SELECT 2,'-',1,null UNION ALL
SELECT 2,'/',1,null
;
update ta
set c =cast( dbo.f_calc(ltrim(a)+op+ltrim(b)) as DECIMAL(9,2))select * from tadrop table ta
drop function f_calc
/*
A OP B C
----------- ---- ----------- ---------------------------------------
2 * 25 50.00
2 + 3 5.00
2 - 1 1.00
2 / 1 2.00(4 行受影响)
*/
exec master..sys.fn_varbintohexstr @errb,@s out
update tt set c=case op when '*' then a*b when '+' then a+b when '-' then a-b when '/' then a/b else 0 end