update #A2 set PF_Qty = molecule / denominator where denominator<>0
update #A2 set PF_Qty = case when denominator=0 then 0 else molecule /denominator end
update #A2 set PF_Qty = case when denominator =0 then 0 else cast(molecule / denominator as decimal(18,20 end
update #A2 set PF_Qty = case denominator when 0 then 0 else molecule /denominator end
--取整: update #A2 set PF_Qty = case denominator when 0 then 0 else cast(molecule/denominator as int) end
molecule/denominator 本身不會取小數吧
哦 如果 molecule denominator 不為整 會有小數 呵呵
SET ARITHABORT OFF SET ANSI_WARNINGS OFF DECLARE @a table(a INT ) INSERT @a SELECT 0 INSERT @a SELECT 1 INSERT @a SELECT 2 INSERT @a SELECT 3 INSERT @a SELECT NULL SELECT 20/a FROM @a SET ARITHABORT ON SET ANSI_WARNINGS ON --result /*----------- NULL 20 10 6 NULL(所影响的行数为 5 行)*/
比如3/4,除出来就已经是0了,再cast也没有用的。所以 update #A2 set PF_Qty = case when denominator=0 then 0 else molecule×1.0 /denominator end
select sum(RefundAmount) 怎么设置保留位数
cast(sum(RefundAmount) as dec(18,2)) --18总长度,2小数位数。总长度最长可为38
sum 求和的数据,怎么转换为Decimal
update #A2 set PF_Qty = IIF(denominator=0,0,INT(molecule /denominator))
celing好像不行吧,这个函数并非是取整的函数,你试下ceiling(3.2/1.00)就会发现问题了,返回时4,而不是3我的代码是: update #A2 set PF_Qty = case denominator when 0 then 0 else cast(molecule/denominator as int) end
case when 分母=0 then 1 else 分母 end csse when 分母=0 then 分子 else 分子 /分母 end
where denominator<>0
set PF_Qty = case when denominator=0 then 0 else molecule /denominator end
cast(molecule / denominator as decimal(18,20 end
--取整:
update #A2 set PF_Qty = case denominator when 0 then 0 else cast(molecule/denominator as int) end
本身不會取小數吧
如果
molecule
denominator
不為整
會有小數
呵呵
SET ANSI_WARNINGS OFF
DECLARE @a table(a INT )
INSERT @a SELECT 0
INSERT @a SELECT 1
INSERT @a SELECT 2
INSERT @a SELECT 3
INSERT @a SELECT NULL SELECT 20/a FROM @a SET ARITHABORT ON
SET ANSI_WARNINGS ON
--result
/*-----------
NULL
20
10
6
NULL(所影响的行数为 5 行)*/
update #A2
set PF_Qty = case when denominator=0 then 0 else molecule×1.0 /denominator end
cast(sum(RefundAmount) as dec(18,2)) --18总长度,2小数位数。总长度最长可为38
update #A2 set PF_Qty = case
denominator when 0 then 0
else cast(molecule/denominator as int)
end