sql除数为零怎么解决!!

Question

NULLIF(sum(a.define26 * a.define34 * d.srate),0)

Answer 1

select sum(isnull(ScrapQty,0)+isnull(Define27,0))/(case sum(a.define26 * a.define34 * d.srate) when 0 then 1000000 else sum(a.define26 * a.define34 * d.srate) end) yy from aa 

Answer 2

select case when sum(a.define26 * a.define34 * d.srate) = 0 then 0 
            else sum(isnull(ScrapQty,0)+isnull(Define27,0))/sum(a.define26 * a.define34 * d.srate) 
       end as yy 
from aa 

Answer 3

NULLIF不行吧除数为0只能自己判断了select 
case when sum(a.define26 * a.define34 * d.srate) = 0 then 0 else 
sum(isnull(ScrapQty,0)+isnull(Define27,0))/sum(a.define26 * a.define34 * d.srate) end as yy from aa 

Answer 4

select sum(isnull(ScrapQty,0)+isnull(Define27,0))/sum(a.define26 * a.define34 * d.srate)as yy from aa 
where
 sum(a.define26 * a.define34 * d.srate) >0

Answer 5

应该可以的。
NULLIF的话，结果是null，要是想得到0，就在最外面加一个ISNULL（XX,0）

Answer 6

select case when sum(a.define26 * a.define34 * d.srate) = 0 then 0 
            else sum(isnull(ScrapQty,0)+isnull(Define27,0))/sum(a.define26 * a.define34 * d.srate) 
       end as yy 
from aa 

Answer 7

select case sum(a.define26 * a.define34 * d.srate) when 0 then 0 else sum(isnull(ScrapQty,0)+isnull(Define27,0))/sum(a.define26 * a.define34 * d.srate) end as yy from aa 

Answer 8

select case when sum(a.define26 * a.define34 * d.srate) = 0 then 除数是0时你的替代值 
            else sum(isnull(ScrapQty,0)+isnull(Define27,0))/sum(a.define26 * a.define34 * d.srate) 
       end as yy 
from aa 

Answer 9

SQL Server2005不是支持try...catch了吗？捕获异常吧

Answer 10

同意用case when语句来处理为0的情况和非0的情况

Answer 11

case when a.define26 * a.define34 * d.srate=0 then null
else a.define26 * a.define34 * d.srate end

Answer 12

COALESCE (a.define26 * a.define34 * d.srate=0,0,sum(isnull(ScrapQty,0)+isnull(Define27,0))/sum(a.define26 * a.define34 * d.srate))

Answer 13

SET ARITHABORT off
SET ANSI_WARNINGS offSELECT 1/0

Answer 14

乌龟是正确的
先判断一下再除
select case when sum(a.define26 * a.define34 * d.srate) = 0 then 0 
            else sum(isnull(ScrapQty,0)+isnull(Define27,0))/sum(a.define26 * a.define34 * d.srate) 
       end as yy 
from aa 

Answer 15

这种方式还真的不错!!输出null