除以两列，但避免除以零

Question

我想用一个连接日志表做这样的事情：

SELECT StartTime, EndTime, EventTime, SentBytes, SentObjects, \
    SentBytes/SentObjects AS bytes_per_object, \
    SentBytes/(EndTime - StartTime) AS bitrate, \
    (EndTime - StartTime) AS duration FROM [table];

但是在某些情况下，StartTime和EndTime可以是相等的(零持续时间)，或者SentObjects可以是零。因此，上面的查询给出了零错误除法。如何重写语句以获得结果？用-1代替零除法是很好的。

我在想类似于三元运算的东西，但我不知道怎么写：

SELECT IFF(SentObjects > 0, SentBytes/SentObjects, -1) AS bytes_per_object FROM table;

SELECT .... WHERE SentObjects > 0不是一个理想的解决方案，因为我希望在一个列表中看到所有的连接。

Server版本10_50

Answer 1

SELECT StartTime, EndTime, EventTime, SentBytes, SentObjects, \
    SentBytes/SentObjects AS bytes_per_object, \
    isNull(SentBytes/nullif((EndTime - StartTime), 0), -1) AS bitrate, \
    (EndTime - StartTime) AS duration FROM [table];

Answer 2

 select 1/nullif(0,0)

SELECT StartTime, EndTime, EventTime, SentBytes, SentObjects,  
    coalesce(SentBytes/NULLIF(SentObjects,0),-1) AS bytes_per_object,  
    coalesce(SentBytes/NULLIF((EndTime - StartTime),0),-1) AS bitrate,  
    (EndTime - StartTime) AS duration FROM [table] 

您可以使用以下函数避免零

Answer 3

SELECT StartTime, EndTime, EventTime, SentBytes, SentObjects,
    SentBytes/(case when isnull(SentObjects, 0) = 0 then -1 else SentObjects end) AS bytes_per_object,
    SentBytes/(case when isnull((EndTime - StartTime), 0) = 0 then -1 else (EndTime - StartTime)end) AS bitrate,
    (EndTime - StartTime) AS duration FROM [table];