create table tb(sm1 int,sm2 int,sm3 int, sm4 int,sm5 int)
insert tb
select 1,2,3,3,3 union all
select 3,3,6,5,4 union all
select 4,4,5,2,2 union all
select 3,3,6,4,2
/*
要求的到一下结果:
1*2*3*3*3+3*3*6*5*4+4*4*5*2*2+3*3*6*4*2
*/
declare @str varchar(8000)
set @str=''
select @str=@str+'+'+cast(sm1 as varchar)+'*'+cast(sm2 as varchar)+'*'
+cast(sm3 as varchar)+'*'+cast(sm4 as varchar)+'*'+cast(sm5 as varchar) from tb
set @str=stuff(@str,1,1,'')
print @str
/*
1*2*3*3*3+3*3*6*5*4+4*4*5*2*2+3*3*6*4*2
*/
insert tb
select 1,2,3,3,3 union all
select 3,3,6,5,4 union all
select 4,4,5,2,2 union all
select 3,3,6,4,2
/*
要求的到一下结果:
1*2*3*3*3+3*3*6*5*4+4*4*5*2*2+3*3*6*4*2
*/
declare @str varchar(8000)
set @str=''
select @str=@str+'+'+cast(sm1 as varchar)+'*'+cast(sm2 as varchar)+'*'
+cast(sm3 as varchar)+'*'+cast(sm4 as varchar)+'*'+cast(sm5 as varchar) from tb
set @str=stuff(@str,1,1,'')
print @str
/*
1*2*3*3*3+3*3*6*5*4+4*4*5*2*2+3*3*6*4*2
*/
insert into @T select 1 ,2 ,3 ,3 ,3
union all select 3, 3 ,6 ,5 ,4
union all select 4 ,4 ,5 ,2 ,2
union all select 3 ,3 ,6 ,4 ,2select [id]=identity(int,1,1),* into tmp_1 from @t
gocreate function result(@id int)
returns int
asbegin
return(select sm1*sm2*sm3*sm4*sm5 from tmp_1 where id=@id)
end
goselect sum(dbo.result(id)) from tmp_1drop table tmp_1
drop function result
??
create table t_zzq_add(
sm1 int null,
sm2 int null,
sm3 int null,
sm4 int null,
sm5 int null
)
insert into t_zzq_add
values(1,2,3,3,3)
insert into t_zzq_add
values(3,3,6,5,4)
insert into t_zzq_add
values(4,4,5,2,2)
insert into t_zzq_add
values(3,3,6,4,2)
*/
select sum(sm1*sm2*sm3*sm4*sm5) from t_zzq_add--1886
insert tb
select 1,2,3,3,3 union all
select 3,3,6,5,4 union all
select 4,4,5,2,2 union all
select 3,3,6,4,2
declare @s varchar(1024)
set @s=''
select @s=@s+rtrim(cast(sm1 as char(10))) + '*' + rtrim(cast(sm2 as char(10))) + '*' + rtrim(cast(sm3 as char(10))) + '*' + rtrim(cast(sm4 as char(10))) + '*' + rtrim(cast(sm5 as char(10))) from tb
print @s