--写个自定义函数吧
create function f_calc(
@num decimal(18,2)
)returns int
as
begin
declare @re int
select @re=round(@num,0)
return(case
when right(@re,1)%3=0 then @re
else left(@re,len(@re)-1)*10+(right(@re,1)+2)/3*3 end)
end
go
create function f_calc(
@num decimal(18,2)
)returns int
as
begin
declare @re int
select @re=round(@num,0)
return(case
when right(@re,1)%3=0 then @re
else left(@re,len(@re)-1)*10+(right(@re,1)+2)/3*3 end)
end
go
解决方案 »
- sql 同一列数据更新问题
- sql脚本生成数据库
- 想在程序中加入一个像SQL SERVER中数据导入导出那样动态显示的齿轮动画,谁能帮忙提供这个动画呀!多谢!
- 两个简单问题
- 如何使用SQL中的OpenDataSource来连接DBF文件。。。在线等待!!
- 请问:sql2000中能否根据表的主外键自动生成关系图?
- 警告: 聚合或其它 SET 操作消除了空值。
- 請教:如何用程序代碼對sql server進行備份與還原?
- 本周六周日上班,元旦3、4、5好还要加班,不开心,送分。
- 请问那位知道:用Access数据,怎样通过SQL语句,新增一个字符型字段,要求字段可以为空字符
- 在SQL中,如何将符合条件的记录锁定为只读,待任务执行完成再解除锁定?
- 用select的时候怎样排除某个字段阿?
create function f_calc(
@num decimal(18,2)
)returns int
as
begin
declare @re int
select @re=round(@num,0)
return(case
when right(@re,1)%3=0 then @re
else left(@re,len(@re)-1)*10+(right(@re,1)+2)/3*3 end)
end
go--调用函数实现转换
select 原值=re,转化结果=dbo.f_calc(re)
from(
select re=1.11
union all select 2.10
union all select 123.89
union all select 134.43
union all select 43.43
union all select 58.43
union all select 60
)a
go--删除测试
drop function f_calc/*--测试结果原值 转化结果
------- -----------
1.11 3
2.10 3
123.89 126
134.43 136
43.43 43
58.43 59
60.00 60(所影响的行数为 7 行)
--*/
case when right(cast(round(price,0) as int),1)%3=0 then 3
else right(cast(round(price,0) as int),1)%3 end )+round(price,0)
when 0 then price
else price+3-(price % 10 ) % 3
end
) from 表
select 原值=re
,转化结果=case
when right(cast(round(re,0) as int),1)%3=0
then cast(round(re,0) as int)
else left(cast(round(re,0) as int),len(cast(round(re,0) as int))-1)*10
+(right(cast(round(re,0) as int),1)+2)/3*3 end
from(
select re=cast(1.11 as decimal(10,2))
union all select 2.10
union all select 123.89
union all select 134.43
union all select 43.43
union all select 58.43
union all select 60
)a
go/*--测试结果原值 转化结果
------- -----------
1.11 3
2.10 3
123.89 126
134.43 136
43.43 43
58.43 59
60.00 60(所影响的行数为 7 行)
--*/
select price=(case (cast(round(price,0) as bigint) % 10 ) % 3
when 0 then price
else cast(round(price,0) as bigint)+3
-cast(round(price,0) as bigint) % 10 ) % 3
end
) from 表
select price=(case (cast(round(price,0) as bigint) % 10 ) % 3
when 0 then cast(round(price,0) as bigint)
else cast(round(price,0) as bigint)+3
-cast(round(price,0) as bigint) % 10 ) % 3
end
) from 表
思路: 先取整,然后除10取余找到个位数,
然后用个位数除3取余,判断如果为0,则为当前price,否则加上3的补数
elseround(price,0)%3 end)
from table
declare @price numeric(18,2)
set @price=0
while @price<10
begin
print ceiling(@price)+(3-cast(ceiling(@price) as int)%10%3)%3
set @price=@price+1
end
返回:0、3、3、3、6、6、6、9、9、9,说明上述表达式正确
declare @price numeric(18,2)
set @price=3.3
select @price,ceiling(@price)+(3-cast(ceiling(@price) as int)%10%3)%3
--结果: 3.30 6
set @price=6.3
select @price,ceiling(@price)+(3-cast(ceiling(@price) as int)%10%3)%3
--结果:6.30 9
alter function udf_getNum(@a float)
returns int
as
begin declare @b int,@c int
if charindex('.',@a)>0
begin
select @b=cast(round(@a,0) as int)
end
else
begin
select @b=@a
end
select @c=case when right(@b,1) in(1,4,7) then @b+2
when right(@b,1)in(2,5,8) then @b+1 else @b end
return @c
end
goselect dbo.udf_getNum(24.8)
--结果
-----------
26(1 row(s) affected)
select price=cast(round(price,0) as bigint)
+(3-cast(round(price,0) as bigint) % 10 ) % 3 )%3
from 表 这个能用
cast(round(@price,0)+(3-cast(round(@price,0) as int)%10%3)%3 as int)
cast(round(@price,0) as int)+(3-cast(round(@price,0) as int)%10%3)%3