表:
create table T(id DECIMAL(10,2))
insert T select 1.01 union all select 2.33 union all select 3.55 union all
select -4.15 union all select -51.5 union all select 6.66SELECT * FROM T序号:ID
1 1.01
2 2.33
3 3.55
4 -4.15
5 -51.50
6 6.66查询:
结果显示为:
序号:结果:
1 1000
2 1000*(2.33)
3 1000*(2.33)*(3.55)
4 1000*(2.33)*(3.55)*(-4.15)
5 1000*(2.33)*(3.55)*(-4.15)*(-51.50)
6 1000*(2.33)*(3.55)*(-4.15)*(-51.50)*( 6.66)
请问使用SQL实现。
在线求助!~~~~~~
create table T(id DECIMAL(10,2))
insert T select 1.01 union all select 2.33 union all select 3.55 union all
select -4.15 union all select -51.5 union all select 6.66SELECT * FROM T序号:ID
1 1.01
2 2.33
3 3.55
4 -4.15
5 -51.50
6 6.66查询:
结果显示为:
序号:结果:
1 1000
2 1000*(2.33)
3 1000*(2.33)*(3.55)
4 1000*(2.33)*(3.55)*(-4.15)
5 1000*(2.33)*(3.55)*(-4.15)*(-51.50)
6 1000*(2.33)*(3.55)*(-4.15)*(-51.50)*( 6.66)
请问使用SQL实现。
在线求助!~~~~~~
create table #T(
id int identity(1,1),Result DECIMAL(10,2))
insert #T (Result ) select 1.01 union all select 2.33 union all select 3.55 union all
select -4.15 union all select -51.5 union all select 6.66SELECT * FROM #T with t as
(select id, cast(1000 as varchar(8000))result from #t where id=1
union all
select a.id, t.result+'*('+CAST(a.result as varchar(20))+')' from #t a join t on a.id=t.id+1
) select * from t
id result
1 1000
2 1000*(2.33)
3 1000*(2.33)*(3.55)
4 1000*(2.33)*(3.55)*(-4.15)
5 1000*(2.33)*(3.55)*(-4.15)*(-51.50)
6 1000*(2.33)*(3.55)*(-4.15)*(-51.50)*(6.66)
with cte as
(
select row_number() over (order by getdate()) as num , id from #t
)select num,result ='1000'+stuff(isnull((select ' * ('+cast(id as varchar(50))+') ' from cte a where a.num>1 and a.num<=b.num for xml path('')),' '),1,1,'') from cte b
/*
num result
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1000
2 1000* (2.33)
3 1000* (2.33) * (3.55)
4 1000* (2.33) * (3.55) * (-4.15)
5 1000* (2.33) * (3.55) * (-4.15) * (-51.50)
6 1000* (2.33) * (3.55) * (-4.15) * (-51.50) * (6.66) (6 row(s) affected)
我描述错误
结果为:
num result
1 1000
2 2330.00
3 8271.5000
4 -34326.725000
5 1767826.33750000
6 11773723.4077500000
请直接返回计算后的值
谢谢!~~
select * from #table
declare @i int,@str float,@strb float
set @str =1
set @i = 1
while @i <= 6
begin
if(@i=1)
begin
print 1000
set @i=@i+1
end
else
begin
select @strb = id from #table where num = @i
set @str =@strb * @str
print 1000*@str
set @i= @i +1
end
end
drop table #table貌似很多问题- - ..随便写的
id int identity(1,1),Result DECIMAL(10,2))
insert #T (Result ) select 1.01 union all select 2.33 union all select 3.55 union all
select -4.15 union all select -51.5 union all select 6.66SELECT * FROM #T ;with t as
(select id, cast(1000 AS dec(28,10)) result from #t where id=1
union all
select a.id, cast(t.result* a.result AS dec(28,10)) from #t a join t on a.id=t.id+1
) select * from t/*
id result
1 1000.0000000000
2 2330.0000000000
3 8271.5000000000
4 -34326.7250000000
5 1767826.3375000000
6 11773723.4077500000
*/改一下就可以了。
id int identity(1,1),Result DECIMAL(10,2))
insert #T (Result ) select 1.01 union all select 2.33 union all select 3.55 union all
select -4.15 union all select -51.5 union all select 6.66SELECT * FROM #T with t as
(select id, result from #t where id=1
union all
select a.id, t.result * a.result from #t a join t on a.id=t.id+1
) select * from t