不足0.5算0.5数据源
select * from (
select 1.00 as b union all
select 2.00 as b union all
select 1.01 as b union all
select 0.01 as b union all
select 0.49 as b union all
select 2.51 as b union all
select 2.99 as b union all
) as tb
得到以下结果1.00=1.00
2.00=2.001.01=1.5
1.49=1.50.01=0.5
0.49=0.52.51=3
2.99=3
select * from (
select 1.00 as b union all
select 2.00 as b union all
select 1.01 as b union all
select 0.01 as b union all
select 0.49 as b union all
select 2.51 as b union all
select 2.99 as b union all
) as tb
得到以下结果1.00=1.00
2.00=2.001.01=1.5
1.49=1.50.01=0.5
0.49=0.52.51=3
2.99=3
解决方案 »
- 求助:配置OLAP链接服务器相关问题
- sql server、access数据库,max函数能用来求字符字段的最大值吗?
- 请教关于EXISTS关键字
- 有一个简单的问题不懂。
- 在2005里面,我想建立一个identity(1,1)类型的用户自定义数据类型,该怎么办?
- 可不可以通过存储过程将一个数据库复制成另一个不同名的数据库?
- 我用程序可以创建了数据源,但是...
- 请问一个订单表结构的设计问题,一份订单有多个产品及对应数量,如把表结构设计为单号产品数量,这单号会大量冗余
- 关于ORACAL修改密码的问题
- 很有意思的问题,大家跨来跨来啊
- 时间计算问题结果要以时间格式显示
- 急求,数据库存储过程问题,谢谢。
(
select 1.00 as b union all
select 2.00 as b union all
select 1.01 as b union all
select 0.01 as b union all
select 0.49 as b union all
select 2.51 as b union all
select 2.99 as b
) tb
when right(b,2) between 1 and 49 then .5
when right(b,2) between 50 and 100
then 1 else 0 end
from (
select 1.00 as b union all
select 2.00 as b union all
select 1.01 as b union all
select 0.01 as b union all
select 0.49 as b union all
select 2.51 as b union all
select 2.99 as b
) as tb
---------------------------------------
1.0
2.0
1.5
0.5
0.5
3.0
3.0(7 行受影响)
when b%1>0.5 then 1 else 0 end) from (
select 1.00 as b union all
select 2.00 as b union all
select 1.01 as b union all
select 0.01 as b union all
select 0.49 as b union all
select 2.51 as b union all
select 2.99 as b
) dd/*
---- ----
1.00 1.0
2.00 2.0
1.01 1.5
0.01 0.5
0.49 0.5
2.51 3.0
2.99 3.0
*/
set @i=2.99
select cast(@i+0.5 as int)+ case when cast(@i+0.5 as int)>=@i then 0 else 0.5 end
(
select 1.00 as b union all
select 2.00 as b union all
select 1.01 as b union all
select 0.01 as b union all
select 0.49 as b union all
select 2.51 as b union all
select 2.50 as b union all
select 2.99 as b
) tb
(
select 1.00 as b union all
select 2.00 as b union all
select 1.01 as b union all
select 0.01 as b union all
select 0.49 as b union all
select 2.51 as b union all
select 2.50 as b union all
select 2.99 as b
) tb
/*
b c
----- ---- ------------
1.00 = 1.000000
2.00 = 2.000000
1.01 = 1.500000
.01 = .500000
.49 = .500000
2.51 = 3.000000
2.50 = 2.500000
2.99 = 3.000000(所影响的行数为 8 行)
*/
select left(b,1)+case
when right(b,2) between 1 and 49 then 0.5
when right(b,2) between 50 and 50 then 0.5
when right(b,2) between 51 and 100
then 1 else 0 end
from (
select 1.00 as b union all
select 2.00 as b union all
select 1.01 as b union all
select 0.01 as b union all
select 0.50 as b union all
select 0.5 as b union all
select 2.50 as b union all
select 2.99 as b
) as tb---------------------------------------
1.0
2.0
1.5
0.5
0.5
0.5
2.5
3.0(8 行受影响)
when b%1.0 >0.5 then cast (b as int)+1
when b%1.0 = 0 then b
else cast (b as int)+0.5 end
from #t1
alter function dbo.f_setvalue(@value decimal(18,2)) returns decimal(18,2)
as
begin
declare @result decimal(18,2)
declare @strvalue varchar(20)
declare @subvalue decimal(18,2)
set @strvalue=cast(@value as varchar(20))
set @subvalue=cast(substring(@strvalue,charindex('.',@strvalue),len(@strvalue)-charindex('.',@strvalue)) as decimal(18,2))
if (@subvalue<=0.5)
begin
set @subvalue=0.5
set @result=cast(substring(@strvalue,1,charindex('.',@strvalue)-1)+'.5' as decimal(18,2))
end
else
set @result=cast(@value as int)+1
return @result
end