declare @t table
(
num1 int
,yunsuanfu1 varchar(10)
,num2 int
,yunsuanfu2 varchar(10)
,num3 int
,yunsuanfu3 varchar(10)
,num4 int
,yunsuanfu4 varchar(10)
,num5 int
)
insert into @t values(1,'+',3,'*',4,'+',5,'/',5)
insert into @t values(1,'+',3,'*',4,null,null,null,null)
insert into @t values(1,'+',3,'*',4,'+',5,null,null)
insert into @t values(1,'-',3,'*',4,'+',5,'/',5)
insert into @t values(1,'*',3,'*',4,'+',5,'/',5)
insert into @t values(1,'/',3,'*',4,'+',5,'/',5)
select * from @t
----------------------------
1.列数是固定的
2.并不是每列都有值，但是保证最后一个不为空的列是数字
3.根据数据表中的运算，得出其结果
4.要求具有很大的灵活性，能适应2所说的情况
-------------------------------------
------解决方案--------------------
MSScriptControl.ScriptControl
去执行运算.
------解决方案--------------------
- SQL code
declare @t table ( num1 int ,yunsuanfu1 varchar(10) ,num2 int ,yunsuanfu2 varchar(10) ,num3 int ,yunsuanfu3 varchar(10) ,num4 int ,yunsuanfu4 varchar(10) ,num5 int ) insert into @t values(1,'+',3,'*',4,'+',5,'/',5) insert into @t values(1,'+',3,'*',4,null,null,null,null) insert into @t values(1,'+',3,'*',4,'+',5,null,null) insert into @t values(1,'-',3,'*',4,'+',5,'/',5) insert into @t values(1,'*',3,'*',4,'+',5,'/',5) insert into @t values(1,'/',3,'*',4,'+',5,'/',5) declare @sql varchar(8000)select @sql=isnull(@sql+' union all ','')+'select '+ isnull(ltrim(num1),'')+isnull(yunsuanfu1,'')+ isnull(ltrim(num2),'')+isnull(yunsuanfu2,'')+ isnull(ltrim(num3),'')+isnull(yunsuanfu3,'')+ isnull(ltrim(num4),'')+isnull(yunsuanfu4,'')+ isnull(ltrim(num5),'') from @tprint @sqlexec(@sql)/**select 1+3*4+5/5 union all select 1+3*4 union all select 1+3*4+5 union all select 1-3*4+5/5 union all select 1*3*4+5/5 union all select 1/3*4+5/5 ----------- 141318-10131**/
------解决方案--------------------
- SQL code
create function f_calc(@str varchar(1000)--要计算的表达式)returns sql_variantasbegindeclare @re sql_variantdeclare @err int,@src varchar(255),@desc varchar(255)declare @obj intexec @err=sp_oacreate 'MSScriptControl.ScriptControl',@obj outif @err<>0 goto lb_errexec @err=sp_oasetproperty @obj,'Language','vbscript'if @err<>0 goto lb_errexec @err=sp_oamethod @obj,'Eval',@re out,@strif @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 outreturn('错误号: [email protected]+char(13)+'错误源: [email protected]+char(13)+'错误描述: [email protected])endgodeclare @t table ( num1 int ,yunsuanfu1 varchar(10) ,num2 int ,yunsuanfu2 varchar(10) ,num3 int ,yunsuanfu3 varchar(10) ,num4 int ,yunsuanfu4 varchar(10) ,num5 int ) insert into @t values(1,'+',3,'*',4,'+',5,'/',5) insert into @t values(1,'+',3,'*',4,null,null,null,null) insert into @t values(1,'+',3,'*',4,'+',5,null,null) insert into @t values(1,'-',3,'*',4,'+',5,'/',5) insert into @t values(1,'*',3,'*',4,'+',5,'/',5) insert into @t values(1,'/',3,'*',4,'+',5,'/',5) select dbo.f_calc(ltrim(num1)+yunsuanfu1+ltrim(num2)+yunsuanfu2+ltrim(num3)+isnull(yunsuanfu3,'')+isnull(ltrim(num4),'')+isnull(yunsuanfu4,'')+isnull(ltrim(num5),'')) from @t drop function f_calc/* ---------------------------------------------------------------------------------------------------------------- 141318-10132.33333333333333*/