create function m (@type int)
returns decimal(10,3)
as
begin
declare @a decimal(10,3)
set @a=1
select @a=@a*value from t where type=@type
return @a
end
--语句:
select distinct type,dbo.m(type) from t
returns decimal(10,3)
as
begin
declare @a decimal(10,3)
set @a=1
select @a=@a*value from t where type=@type
return @a
end
--语句:
select distinct type,dbo.m(type) from t
Type int,
[Values] numeric(10,2)
)insert tableX
select
1 , 0.6
union
select
1 , 0.7
union
select
1 , 0.9
union
select
2 , 0.5
union
select
2 , 0.9
union
select
3 , 0.6
union
select
3 , 0.8
create function getValues(@Type Int)
returns numeric(10,6)
as
begin
declare @Rec numeric(10,6)
set @Rec=1
select @Rec=@Rec*[values] from tableX
where type=@type
return @Rec
end
go
调用:
select distinct Type,dbo.getValues(Type) as [values]
from tableX结果:
type values
------ ---------
1 .378000
2 .450000
3 .480000
CREATE FUNCTION mulvalue(@type integer)
RETURNS float
ASBEGINDECLARE @mul float,@val float
SET @mul=1.0
DECLARE cur CURSOR FOR SELECT value FROM t WHERE type=@type
OPEN cur
FETCH NEXT FROM cur INTO @val
WHILE @@FETCH_STATUS=0
BEGIN
IF @val IS NOT NULL
SET @mul=@mul*@val
FETCH NEXT FROM cur INTO @val
END
CLOSE cur
DEALLOCATE curRETURN @mul
END--调用函数
SELECT [type],dbo.mulvalue(type) FROM t GROUP BY [type]你试试看。
为什么“注意只有2000能用:”
是指函数还是调用函数的SQL语句,不知能不能在Sybase ASE 12.5中使用?