怎么计算两列相乘以新一列出现?
如何计算两列相乘以新一列出现???
SQL2000数据库,如何计算guige乘以数量后的结果,以select id,guige,shuliang,(guige*shuliang) as heji 的方式选出?
产品信息规格是这样的方式,怎么已上面的方式选出呢?
ID guige shuliang
1 1*5 5.2
2 1.0/5 3
3 1/5 5
4 2*5+3 8
5 (8-5)*3 -5.213
------解决方案--------------------
SQL2000数据库,如何计算guige乘以数量后的结果,以select id,guige,shuliang,(guige*shuliang) as heji 的方式选出?
产品信息规格是这样的方式,怎么已上面的方式选出呢?
ID guige shuliang
1 1*5 5.2
2 1.0/5 3
3 1/5 5
4 2*5+3 8
5 (8-5)*3 -5.213
------解决方案--------------------
- SQL code
--> 测试数据:[tbl] go if object_id('[tbl]') is not null drop table [tbl] go create table [tbl]( [ID] int, [guige] varchar(11), [shuliang] numeric(5,3) ) go insert [tbl] select 1,'1*5',5.2 union all select 2,'1.0/5',3 union all select 3,'1/5',5 union all select 4,'2*5+3',8 union all select 5,'(8-5)*3',-5.213 declare @minid int declare @maxid int select @minid=MIN(id) from tbl select @maxid=max(id) from tbl go declare @sql varchar(max) set @sql='' declare @minid int select @minid=MIN(ID) from tbl declare @maxid int select @maxid=max(ID) from tbl declare @str varchar(1000) set @str='' while @minid<=@maxid begin select @str='('+[guige]+')' from tbl where ID=@minid select @str='select id,guige,shuliang,' +@str+'*[shuliang] as heji from tbl where ID='+cast(@minid as varchar) print @str set @sql=@sql+' union all '+@str set @minid=@minid+1 end print @sql set @sql=RIGHT(@sql,LEN(@sql)-10) exec(@sql) /* id guige shuliang heji 1 1*5 5.200 26.000000000 2 1.0/5 3.000 0.600000000 3 1/5 5.000 0.000000000 4 2*5+3 8.000 104.000000000 5 (8-5)*3 -5.213 -46.917000000 */
------解决方案--------------------
- SQL code
declare @T table (ID int,guige char(7),shuliang decimal(18,3)) insert into @T select 1,'1*5',5.2 union all select 2,'1.0/5',3 union all select 3,'1/5',5 union all select 4,'2*5+3',8 union all select 5,'(8-5)*3',-5.213 select *,dbo.m_charcompute(guige+'*('+ltrim(shuliang)+')') as newcol from @T /* ID guige shuliang newcol ----------- ------- --------------------------------------- ---------------------- 1 1*5 5.200 26 2 1.0/5 3.000 0.6 3 1/5 5.000 1 4 2*5+3 8.000 34 5 (8-5)*3 -5.213 -46.917 */ create function [dbo].[m_charcompute](@bds varchar(1000)) returns float as BEGIN set @bds = replace(@bds,' ','')--去空格,免得麻烦。 declare @i int,@j int declare @c1 char(1),@c2 char(1),@c varchar(100) declare @v1 float,@v2 float,@v float declare @t table(id int identity(1,1),s varchar(100)) declare @s table(id int identity(1,1),s varchar(100)) declare @sv table(id int identity(1,1),v float) select @i = 0,@j = len(@bds),@c2 = '',@c = '' while @i<@j begin select @c1 = @c2,@i = @i+1 select @c2 = substring(@bds,@i,1) if charindex(@c2,'.0123456789') > 0 or (@c2 = '-' and @c1 in('','*','-','+','/','(')) begin select @c = @c + @c2 continue end if @c <> '' begin insert @t(s) select @c select @c = '' end if charindex(@c2,')')>0 begin insert @t(s) select s from @s where id > isnull((select max(id) from @s where s in('(')),0) order by id desc delete @s where id >= isnull((select max(id) from @s where s in('(')),0) continue end if charindex(@c2,'+-)')>0 begin insert @t(s) select s from @s where id > isnull((select max(id) from @s where s in('(')),0) order by id desc delete @s where id > isnull((select max(id) from @s where s in('(')),0) if @c2 <> ')' insert @s(s) select @c2 continue end if charindex(@c2,'*/')>0 begin insert @t(s) select s from @s where id > isnull((select max(id) from @s where s in('(','+','-')),0) order by id desc delete @s where id > isnull((select max(id) from @s where s in('(','+','-')),0) insert @s select @c2 continue end if charindex(@c2,'(')>0 insert @s select @c2 end if @c <> '' insert @t(s) select @c insert @t(s) select s from @s order by id desc select @i = 0,@j = max(id) from @t while @i < @j begin select @i = @i + 1 select @c = s from @t where id = @i if @c = '(' continue if @c not in('*','-','+','/') begin insert @sv(v) select convert(float,@c) continue end select @v2 = v from @sv delete @sv where id = (select max(id) from @sv) select @v1 = v from @sv delete @sv where id = (select max(id) from @sv) select @v = case @c when '+' then @v1 + @v2 when '-' then @v1 - @v2 when '*' then @v1 * @v2 when '/' then @v1 / @v2 end insert @sv(v) select @v end select @v = v from @sv return @v end