/* 分解公式 将公式按运算分解到表中 */ declare @str varchar(100) declare @i int,@str1 varchar(10),@str2 varchar(10)set @str='3+7+12-13+8-9*123' --要分解的字符串 declare @tb table(num varchar(20),operation varchar(2)) --定义保存结果的表--公式分拆 set @i=patindex('%[^0-9]%',@str) while @i>0 begin select @str1=left(@str,@i-1) ,@str=substring(@str,@i,8000) ,@i=patindex('%[0-9]%',@str) ,@str2=left(@str,@i-1) ,@str=substring(@str,@i,8000) ,@i=patindex('%[^0-9]%',@str) insert into @tb values(@str1,@str2) end if @str<>'' insert into @tb values(@str,'')--显示分拆结果 select * from @tb
if object_id('[tb]') is not null drop table [tb] go create table [tb] (aa int,bb nvarchar(14)) insert into [tb] select 1,'(3+5)*8' union all select 2,'11+3' union all select 3,'45-22' declare @sql varchar(8000) set @sql='' select @sql=@sql+'select aa='+ltrim(aa)+', bb='+bb+' union all ' from tb set @sql=left(@sql,len(@sql)-10) exec(@sql) /* aa bb ----------- ----------- 1 64 2 14 3 23(3 個資料列受到影響) */
if object_id('[tb]') is not null drop table [tb] go create table [tb] (aa int,bb nvarchar(14)) insert into [tb] select 1,'(3+5)*8' union all select 2,'11+3' union all select 3,'45-22' declare @t table(ID int, val dec(18,2)) declare @s varchar(50),@id int declare cur cursor for select aa,bb from tb open cur fetch next from cur into @id,@s while @@fetch_status=0 begin insert @t exec('select '+@ID+','+@s) fetch next from cur into @id,@s end close cur deallocate cur select * from @t /* ID val ----------- --------------------------------------- 1 64.00 2 14.00 3 23.00(3 個資料列受到影響) */
CREATE TABLE gg_g(a INT,b varchar(20),Result float) insert gg_g select 1 ,'(3+5)*8',NULL union all select 2 ,'11+3' ,NULL union all select 3 ,'45-22' ,NULL GO MASTER..xp_execresultset 'select ''update gg_g set Result= ''+b+'' where a=''+ltrim(a) from gg_g',N'test'SELECT * FROM gg_g--result /*a b Result ----------- -------------------- ----------------------------------------------------- 1 (3+5)*8 64.0 2 11+3 14.0 3 45-22 23.0(所影响的行数为 3 行)*/
应该是这个的效率最高,但是为什么我这里运行时出错呢? Unable to get a bound connection back to server
MSSQL計算表達式的值常見解決方案
分解公式
将公式按运算分解到表中
*/
declare @str varchar(100)
declare @i int,@str1 varchar(10),@str2 varchar(10)set @str='3+7+12-13+8-9*123' --要分解的字符串
declare @tb table(num varchar(20),operation varchar(2)) --定义保存结果的表--公式分拆
set @i=patindex('%[^0-9]%',@str)
while @i>0
begin
select @str1=left(@str,@i-1)
,@str=substring(@str,@i,8000)
,@i=patindex('%[0-9]%',@str)
,@str2=left(@str,@i-1)
,@str=substring(@str,@i,8000)
,@i=patindex('%[^0-9]%',@str)
insert into @tb values(@str1,@str2)
end
if @str<>'' insert into @tb values(@str,'')--显示分拆结果
select * from @tb
go
create table [tb] (aa int,bb nvarchar(14))
insert into [tb]
select 1,'(3+5)*8' union all
select 2,'11+3' union all
select 3,'45-22'
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+'select aa='+ltrim(aa)+', bb='+bb+' union all ' from tb
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
/*
aa bb
----------- -----------
1 64
2 14
3 23(3 個資料列受到影響)
*/
go
create table [tb] (aa int,bb nvarchar(14))
insert into [tb]
select 1,'(3+5)*8' union all
select 2,'11+3' union all
select 3,'45-22'
declare @t table(ID int, val dec(18,2))
declare @s varchar(50),@id int
declare cur cursor for select aa,bb from tb
open cur
fetch next from cur into @id,@s
while @@fetch_status=0
begin
insert @t exec('select '+@ID+','+@s)
fetch next from cur into @id,@s
end
close cur
deallocate cur
select * from @t
/*
ID val
----------- ---------------------------------------
1 64.00
2 14.00
3 23.00(3 個資料列受到影響)
*/
CREATE TABLE gg_g(a INT,b varchar(20),Result float)
insert gg_g select 1 ,'(3+5)*8',NULL
union all select 2 ,'11+3' ,NULL
union all select 3 ,'45-22' ,NULL GO
MASTER..xp_execresultset 'select ''update gg_g set Result= ''+b+'' where a=''+ltrim(a) from gg_g',N'test'SELECT * FROM gg_g--result
/*a b Result
----------- -------------------- -----------------------------------------------------
1 (3+5)*8 64.0
2 11+3 14.0
3 45-22 23.0(所影响的行数为 3 行)*/
应该是这个的效率最高,但是为什么我这里运行时出错呢?
Unable to get a bound connection back to server