寫個存儲過程求出來吧--測試
create table tb(A int,B numeric(10,2))
Insert into tb
select 1,0.3
union all select 1,0.5
union all select 1,1
union all select 2,2
union all select 2,2.3
union all select 2,3.2
union all select 2,0.68
union all select 3,2.3
union all select 3,5.2select * from tb--臨時表
create table tem(id int identity(1,1) ,B int)
insert into tem(B)
select distinct A from tb order by A
select * from tem--結果表
create table tb1(A int,B numeric(10,2))declare @i int,@maxid int
declare @s varchar(100),@id int
select @i=1
select @maxid=max(id) from temset nocount on
while @i<=@maxid
begin
--set @id=''
set @s=1
select @id=B from tem where id=@i
select @s=@s*[B] from tb where A=@id
insert into tb1 values(@id,@s)
set @i=@i+1
end--刪除表
drop table tb
drop table tem
drop table tb1--結果
select * from tb1
A B
---------------------------------
1 .15
2 10.01
3 11.96
create table tb(A int,B numeric(10,2))
Insert into tb
select 1,0.3
union all select 1,0.5
union all select 1,1
union all select 2,2
union all select 2,2.3
union all select 2,3.2
union all select 2,0.68
union all select 3,2.3
union all select 3,5.2select * from tb--臨時表
create table tem(id int identity(1,1) ,B int)
insert into tem(B)
select distinct A from tb order by A
select * from tem--結果表
create table tb1(A int,B numeric(10,2))declare @i int,@maxid int
declare @s varchar(100),@id int
select @i=1
select @maxid=max(id) from temset nocount on
while @i<=@maxid
begin
--set @id=''
set @s=1
select @id=B from tem where id=@i
select @s=@s*[B] from tb where A=@id
insert into tb1 values(@id,@s)
set @i=@i+1
end--刪除表
drop table tb
drop table tem
drop table tb1--結果
select * from tb1
A B
---------------------------------
1 .15
2 10.01
3 11.96
create table t(A int,B numeric(10,2))
insert into t select 1,0.3
union all select 1,0.5
union all select 1,1
union all select 2,2
union all select 2,2.3
union all select 2,3.2
union all select 2,0.68
union all select 3,2.3
union all select 3,5.2
go--建函数执行运算
create function f_qry(@A int)
returns numeric(10,2)
as
begin
declare @s numeric(10,2)
set @s=1
select @s=@s*B from t where A=@A return @s
end
go--返回结果
select distinct A,dbo.f_qry(A) from t--删除对象
drop table t
drop function f_qry
ALTER function f_try(@a int)
returns decimal(8,2)
as
begin
declare @b float
set @b=1
select @b=@b*B from 表 where A=@a
return(@b)
end--查询
select A,dbo.f_try(A) as B from 表 group by A