Ikkita ustunni ajratib qo'ying, lekin bo'linishni nolga solmang

Men aloqa bog'lash jadvali bilan shug'ullanmoqchiman:

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

Ammo ba'zi hollarda StartTime va EndTime teng (nolinchi muddat) bo'lishi mumkin, yoki SentObjects nol bo'lishi mumkin. Shunday qilib, yuqoridagi so'rovlar nolinchi xato bilan bo'linishni beradi. Natijani olish uchun bayonotni qanday yozish mumkin? Bo'limlarni -1 bilan nolga almashtirish yaxshi.

Men bu uch tomonlama operatsiya kabi bir narsa deb o'ylayman, lekin qanday yozishni bilmayman:

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

SELECT .... WHERE SentObjects > 0 is not a desireable solution, as I want to see all the connections in one list.

SQL Server versiyasi 10_50

1

6 javoblar

 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] 

quyidagi funktsiyani ishlatib, noldan qochishingiz mumkin

1
qo'shib qo'ydi

Bundan tashqari, Umumiy jadval ifodasi (CTE) dan foydalanishingiz mumkin: (Sinovsiz skript)

Idea, CTE ichidagi ustun qiymatlarini oldindan baholash va tanlangan bayonotda yangilangan qiymatdan foydalanish.

;With CTE 
AS(
    SELECT StartTime, EndTime, EventTime, 
           IIF(SentObjects > 0, SentBytes, 1) AS SentBytesUpdated, \
           IIF(SentObjects > 0, SentObjects, -1) AS SentObjectsUpdated , \
           SentBytes/(EndTime - StartTime) AS bitrate, \
           (EndTime - StartTime) AS Duration \
    FROM [Table]
)SELECT StartTime, EndTime, EventTime, \
    SentBytesUpdated/SentObjectsUpdated AS bytes_per_object, \
    bitrate \
    Duration \
FROM [CTE]
0
qo'shib qo'ydi
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];
0
qo'shib qo'ydi

Siz WHEN ... THEN ... ELSE ... END dan foydalanishingiz mumkin:

SELECT StartTime, EndTime, EventTime, SentBytes, SentObjects,
    CASE WHEN SentObjects = 0 THEN -1 ELSE SentBytes/SentObjects END AS bytes_per_object,
    CASE WHEN EndTime = StartTime THEN -1 ELSE SentBytes/(EndTime - StartTime) END AS bitrate,
    (EndTime - StartTime) AS duration
FROM [table];
0
qo'shib qo'ydi
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];
0
qo'shib qo'ydi
CASE
WHEN SentObjects > 0 THEN SentBytes/SentObjects
ELSE -1
END AS bytes_per_object
0
qo'shib qo'ydi