有一张表两个字段
a字段 varchar(30) 存储是一些数学公式,如8*9-1+6 4*8/6+9 4+9-2*9等等
b字段Int 类
有什么方法能快速计算出a字段公式的值,并且update b字段
注:请不要回答逐条修改。
3 个解决方案
#1
use master
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](a varchar(50))
insert [tb]
select '8*9-1+6' union all
select '4*8/6+9' union all
select '4+9-2*9'
declare @sql varchar(8000)
select
@sql=ISNULL(@sql+' union all ','')
+'select '''+a+''' as a,'+a+' as b '
from tb
exec(@sql)
/*
a b
8*9-1+6 77
4*8/6+9 14
4+9-2*9 -5
*/
#2
-- 创建表
CREATE TABLE [dbo].[T003](
[id] [int] NOT NULL,
[Formula] [nvarchar](50) NULL,
[value] [int] NULL,
CONSTRAINT [PK_T003] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- 数据:
insert into T003 values (1, '8*9-1+6', 0)
insert into T003 values (1, '4*8/6+9', 0)
insert into T003 values (1, '4+9-2*9', 0)
-- 计算出a字段公式的值,并且update b字段
declare @id varchar(20)
declare @formula varchar(50)
declare @sqlstr varchar(60)
declare @value int
declare cur cursor for
select id, formula from T003
open cur fetch next from cur into @id, @formula
while (@@fetch_status=0)
begin
set @sqlstr = 'update T003 set value = (select '
+ @formula + ')where id = ' + @id ;
exec(@sqlstr)
fetch next from cur into @id, @formula
end
close cur
deallocate cur
-- 结果查看
select * from T003
1 8*9-1+6 77
2 4*8/6+9 14
3 4+9-2*9 -5
#3
谢谢两位,已经解决了。
#1
use master
go
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](a varchar(50))
insert [tb]
select '8*9-1+6' union all
select '4*8/6+9' union all
select '4+9-2*9'
declare @sql varchar(8000)
select
@sql=ISNULL(@sql+' union all ','')
+'select '''+a+''' as a,'+a+' as b '
from tb
exec(@sql)
/*
a b
8*9-1+6 77
4*8/6+9 14
4+9-2*9 -5
*/
#2
-- 创建表
CREATE TABLE [dbo].[T003](
[id] [int] NOT NULL,
[Formula] [nvarchar](50) NULL,
[value] [int] NULL,
CONSTRAINT [PK_T003] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- 数据:
insert into T003 values (1, '8*9-1+6', 0)
insert into T003 values (1, '4*8/6+9', 0)
insert into T003 values (1, '4+9-2*9', 0)
-- 计算出a字段公式的值,并且update b字段
declare @id varchar(20)
declare @formula varchar(50)
declare @sqlstr varchar(60)
declare @value int
declare cur cursor for
select id, formula from T003
open cur fetch next from cur into @id, @formula
while (@@fetch_status=0)
begin
set @sqlstr = 'update T003 set value = (select '
+ @formula + ')where id = ' + @id ;
exec(@sqlstr)
fetch next from cur into @id, @formula
end
close cur
deallocate cur
-- 结果查看
select * from T003
1 8*9-1+6 77
2 4*8/6+9 14
3 4+9-2*9 -5
#3
谢谢两位,已经解决了。