34,593
社区成员
发帖
与我相关
我的任务
分享
sum(case when day(OQCTime)=1 then BadNo else 0 end)/sum(case when day(OQCTime)=1 then floor(TestNo) else 0 end)*1000000 as [1],
提示错误:遇到以零作除数错误
sum(case when day(OQCTime)=1 then BadNo else 0 end)/nullif(sum(case when day(OQCTime)=1 then isnull(floor(TestNo),0) else 0 end)*1000000,0) as [1],
错误提示:将 expression 转换为数据类型 int 时出现算术溢出错误。
sum(case when day(OQCTime)=2 then BadNo else 0 end)/sum(case when day(OQCTime)=2 then floor(TestNo) else 0 end)*1000000 as [2]
结果:0
--思路是这样的
declare @t table(a int,b dec(18,2))
insert @t select 1,0
insert @t select 2,3
insert @t select 3,0
insert @t select 4,6
select case when b=0 then 0 else a/b end [a/b结果]
from @t
/*
a/b结果
---------------------------------------
0.0000000000000000000
0.6666666666666666666
0.0000000000000000000
0.6666666666666666666
(4 row(s) affected)
*/
sum(case when day(OQCTime)=1 then BadNo else 0 end)
得到结果是:1
sum(case when day(OQCTime)=1 then floor(TestNo) else 0 end)
得到结果是:9802
case when sum(case when day(OQCTime)=1 then floor(TestNo) else 0 end)=0 then 0
else (
sum(case when day(OQCTime)=1 then BadNo else 0 end)
/sum(case when day(OQCTime)=1 then floor(TestNo) else 0 end)*1000000) end as [1],