CREATE FUNCTION dbo.GetValue ( @Value FLOAT, --输入数字 @NUM INT, --小数位数 @ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数 ) RETURNS FLOAT AS BEGIN declare @Result float [code=SQL] if @ComputType=1 or @ComputType=0 set @Result= ROUND(@Value,@NUM) if @ComputType=2 set @Result=ROUND(@Value,@NUM,1)+1.0/power(10,@NUM) if @ComputType=3 set @Result= ROUND(@Value,@NUM,1) return @Result END[/code]
上面贴乱了重新贴下 呵呵 CREATE FUNCTION dbo.GetValue ( @Value FLOAT, --输入数字 @NUM INT, --小数位数 @ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数 ) RETURNS FLOAT AS BEGIN declare @Result float
if @ComputType=1 or @ComputType=0 set @Result= ROUND(@Value,@NUM) if @ComputType=2 set @Result=ROUND(@Value,@NUM,1)+1.0/power(10,@NUM) if @ComputType=3 set @Result= ROUND(@Value,@NUM,1) return @Result END
CREATE FUNCTION dbo.GetValue ( @Value DECIMAL(38,9), --输入数字 @NUM INT, --小数位数 @ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数 ) RETURNS DECIMAL(38,9) AS BEGIN DECLARE @R DECIMAL(38,9) IF @ComputType=1 SET @R= ROUND(@value,@NUM) ELSE IF @ComputType=2 SET @R= CEILING(@value*POWER(10,@NUM))/POWER(10,@NUM) ELSE IF @ComputType=3 SET @R= FLOOR(@value*POWER(10,@NUM))/POWER(10,@NUM) ELSE SET @R= @value RETURN @REND
CREATE FUNCTION dbo.GetValue ( @Value FLOAT, --输入数字 @NUM INT, --小数位数 @ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数 ) RETURNS FLOAT AS BEGIN declare @d float if (@ComputType = 1 ) set @d = round(@value,@num) if (@ComputType = 2 ) set @d = ceiling(@value*100)/100.00 if (@ComputType = 3) set @d = FLOOR (@value) return @d END go
直接进1 ? CREATE FUNCTION dbo.GetValue ( @Value FLOAT, --输入数字 @NUM INT, --小数位数 @ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数 ) RETURNS FLOAT AS BEGIN declare @d float if (@ComputType = 1 ) set @d = round(@value,@num) if (@ComputType = 2 ) set @d = ceiling(@value) if (@ComputType = 3) set @d = FLOOR (@value) return @d END go
CREATE FUNCTION dbo.GetValue ( @Value FLOAT, --输入数字 @NUM INT, --小数位数 @ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数 ) RETURNS FLOAT AS BEGIN declare @s varchar(100) set @s=rtrim(@Value) --无小数部分,直接返回原值 if charindex('.',@s)=0 return @Value--结果无小数部分 if @NUM<=0 return left(@s,charindex('.',@s)-1) +case @ComputType when 1 then case when cast(substring(@s,charindex('.',@s)+1,1) as int)<5 then 0 else 1 end when 2 then 1 when 3 then 0 end--结果有小数部分 return left(@s,charindex('.',@s)+@NUM-1) +rtrim(substring(@s,charindex('.',@s)+@NUM,1) +case @ComputType when 1 then case when cast(substring(@s,charindex('.',@s)+@NUM+1,1) as int)<5 then 0 else 1 end when 2 then 1 when 3 then 0 end ) END goselect dbo.GetValue(18.756,2,1) --18.76 select dbo.GetValue(18.756,2,2) --18.76 select dbo.GetValue(18.756,2,3) --18.75 select dbo.GetValue(18.756,0,1) --19 select dbo.GetValue(18.756,0,2) --19 select dbo.GetValue(18.756,0,3) --18
drop FUNCTION dbo.GetValue go CREATE FUNCTION dbo.GetValue ( @Value FLOAT, --输入数字 @NUM INT, --小数位数 @ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数 ) RETURNS FLOAT AS BEGIN declare @i int,@T FLOAT,@result FLOAT set @i=0 set @T=0.5 if(@ComputType=1) set @result=round(@Value,@NUM) else begin while(@i<@NUM) begin set @T=@T*0.1 set @i=@i+1 end if(@ComputType=2) set @result=round(@Value+@T,@NUM) else set @result=round(@Value-@T,@NUM) endreturn @result END go
drop FUNCTION dbo.GetValue go CREATE FUNCTION dbo.GetValue ( @Value FLOAT, --输入数字 @NUM INT, --小数位数 @ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数 ) RETURNS FLOAT AS BEGIN declare @i int,@T FLOAT,@result FLOAT set @i=0 set @T=1 if(@ComputType=1) set @result=round(@Value,@NUM) else begin while(@i<@NUM) begin set @T=@T*10 set @i=@i+1 end if(@ComputType=2) begin if(@Value*@T-cast(@Value*@T as int)>0) set @result=(cast(@Value*@T as int)+1)/@T else set @result=(cast(@Value*@T as int))/@T end else set @result=(cast(@Value*@T as int))/@T endreturn @result END go
(
@Value FLOAT, --输入数字
@NUM INT, --小数位数
@ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数
)
RETURNS FLOAT
AS
BEGIN
declare @Result float
[code=SQL]
if @ComputType=1 or @ComputType=0
set @Result= ROUND(@Value,@NUM)
if @ComputType=2
set @Result=ROUND(@Value,@NUM,1)+1.0/power(10,@NUM)
if @ComputType=3
set @Result= ROUND(@Value,@NUM,1)
return @Result
END[/code]
(
@Value FLOAT, --输入数字
@NUM INT, --小数位数
@ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数
)
RETURNS FLOAT
AS
BEGIN
declare @Result float
if @ComputType=1 or @ComputType=0
set @Result= ROUND(@Value,@NUM)
if @ComputType=2
set @Result=ROUND(@Value,@NUM,1)+1.0/power(10,@NUM)
if @ComputType=3
set @Result= ROUND(@Value,@NUM,1)
return @Result
END
CREATE FUNCTION dbo.GetValue
(
@Value DECIMAL(38,9), --输入数字
@NUM INT, --小数位数
@ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数
)
RETURNS DECIMAL(38,9)
AS
BEGIN
DECLARE @R DECIMAL(38,9) IF @ComputType=1
SET @R= ROUND(@value,@NUM)
ELSE IF @ComputType=2
SET @R= CEILING(@value*POWER(10,@NUM))/POWER(10,@NUM)
ELSE IF @ComputType=3
SET @R= FLOOR(@value*POWER(10,@NUM))/POWER(10,@NUM)
ELSE
SET @R= @value
RETURN @REND
(
@Value FLOAT, --输入数字
@NUM INT, --小数位数
@ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数
)
RETURNS FLOAT
AS
BEGIN
declare @d float
if (@ComputType = 1 )
set @d = round(@value,@num)
if (@ComputType = 2 )
set @d = ceiling(@value*100)/100.00
if (@ComputType = 3)
set @d = FLOOR (@value)
return @d
END
go
CREATE FUNCTION dbo.GetValue
(
@Value FLOAT, --输入数字
@NUM INT, --小数位数
@ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数
)
RETURNS FLOAT
AS
BEGIN
declare @d float
if (@ComputType = 1 )
set @d = round(@value,@num)
if (@ComputType = 2 )
set @d = ceiling(@value)
if (@ComputType = 3)
set @d = FLOOR (@value)
return @d
END
go
(
@Value FLOAT, --输入数字
@NUM INT, --小数位数
@ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数
)
RETURNS FLOAT
AS
BEGIN
declare @s varchar(100)
set @s=rtrim(@Value)
--无小数部分,直接返回原值
if charindex('.',@s)=0 return @Value--结果无小数部分
if @NUM<=0
return left(@s,charindex('.',@s)-1)
+case @ComputType
when 1 then case when cast(substring(@s,charindex('.',@s)+1,1) as int)<5 then 0 else 1 end
when 2 then 1
when 3 then 0
end--结果有小数部分
return left(@s,charindex('.',@s)+@NUM-1)
+rtrim(substring(@s,charindex('.',@s)+@NUM,1)
+case @ComputType
when 1 then case when cast(substring(@s,charindex('.',@s)+@NUM+1,1) as int)<5 then 0 else 1 end
when 2 then 1
when 3 then 0
end
)
END
goselect dbo.GetValue(18.756,2,1) --18.76
select dbo.GetValue(18.756,2,2) --18.76
select dbo.GetValue(18.756,2,3) --18.75
select dbo.GetValue(18.756,0,1) --19
select dbo.GetValue(18.756,0,2) --19
select dbo.GetValue(18.756,0,3) --18
还SUBSTRING一大堆...哎....真的得多泡下CSDN.
ROUND(@Value,@NUM,1)
go
CREATE FUNCTION dbo.GetValue
(
@Value FLOAT, --输入数字
@NUM INT, --小数位数
@ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数
)
RETURNS FLOAT
AS
BEGIN
declare @i int,@T FLOAT,@result FLOAT
set @i=0
set @T=0.5
if(@ComputType=1)
set @result=round(@Value,@NUM)
else
begin
while(@i<@NUM)
begin
set @T=@T*0.1
set @i=@i+1
end
if(@ComputType=2)
set @result=round(@Value+@T,@NUM)
else
set @result=round(@Value-@T,@NUM)
endreturn @result
END
go
drop FUNCTION dbo.GetValue
go
CREATE FUNCTION dbo.GetValue
(
@Value FLOAT, --输入数字
@NUM INT, --小数位数
@ComputType INT --1,四舍五入 2,直接进1 3,去掉尾数
)
RETURNS FLOAT
AS
BEGIN
declare @i int,@T FLOAT,@result FLOAT
set @i=0
set @T=1
if(@ComputType=1)
set @result=round(@Value,@NUM)
else
begin
while(@i<@NUM)
begin
set @T=@T*10
set @i=@i+1
end
if(@ComputType=2)
begin
if(@Value*@T-cast(@Value*@T as int)>0)
set @result=(cast(@Value*@T as int)+1)/@T
else
set @result=(cast(@Value*@T as int))/@T
end
else
set @result=(cast(@Value*@T as int))/@T
endreturn @result
END
go