时间戳

首页 > 解决方案 > Sql 中的除法返回 0 或 NULL

sql - Sql 中的除法返回 0 或 NULL

问题描述

SQL 新手并试图解决这个问题。我期待一个获胜百分比的比率，但我得到一个 0 或空列

With previous as (select Venue, Row,

Count(case when place = 1 then 1 else null end) as firstx
Count(case when place = 2 then 1 else null end) as secondx
Count(case when place = 3 then 1 else null end) as thirdx 
Count(case when place Between 1 and 15 then 1 else null end) as allplaces 

From horses
Where racetitle not like ‘%stand%’
Group by venue, row)

Select case
When firstx = 0
Then Null
Else round(firstx/allplaces, 2)
End as winratio
From previous
Where allplaces > 10;

我想用 secondx 和 thirdx 做到这一点，但不知道为什么它返回一个列为 0 或 Null ？？？

标签： sqlcase

解决方案

您得到的NULL原因是firstx = 0并且0因为您使用的 rdbms 在除法时进行整数除法，所以在除法之前firstx/allplaces乘以：1.0

With previous as (
  select Venue, Row,
         Count(case when place = 1 then 1 end) as firstx
         Count(case when place = 2 then 1 end) as secondx
         Count(case when place = 3 then 1 end) as thirdx 
         Count(case when place Between 1 and 15 then 1 end) as allplaces 
  From horses
  Where racetitle not like ‘%stand%’
  Group by venue, row
)
Select case
  When firstx = 0 Then Null
  Else round(1.0 * firstx/allplaces, 2)
End as winratio
From previous
Where allplaces > 10;

我还简化了CASE里面的表达式，COUNT()因为没有必要，else NULL因为这是默认行为。

推荐阅读