选择"语句比率计算未按预期工作"

Question

我正在尝试创建一个查询，该查询将根据两列中的某些计数进行比率计算，但除非表中有数据，否则它将无法计算比率。

为了缓解这种情况，我希望我的脚本将所有null值转换为1。

这是我当前的脚本(我将删除其余的联接，因为它没有必要包含(

SELECT DISTINCT TB1.Person,
TB2.AllocatedCount as AllocatedCount,
TB3.UnallocatedCount as UnallocatedCount,
AllocatedCount * 1.0 / UnallocatedCount * 1.0 as Ratio,

如果表中有两列的数据，上述结果将产生以下完美的结果：

比率<10000000000000><20000000000000>null

人员	AllocatedCount	未分配计数
A	1	1
B	2	1
C	null

在计算比率时，还需要检查NULL值(此处使用ISNULL()可能是更好的选择(：

SELECT 
DISTINCT TB1.Person,
ISNULL(TB2.AllocatedCount, 1) AS AllocatedCount,
ISNULL(TB3.UnallocatedCount, 1) AS UnallocatedCount,
ISNULL(TB2.AllocatedCount, 1) * 1.0 / ISNULL(TB3.UnallocatedCount, 1) * 1.0 AS Ratio
FROM 
...

我会使用ISNULL

SELECT DISTINCT TB1.Person,
isnull(TB2.AllocatedCount,1) as AllocatedCount,
isnull(TB3.UnallocatedCount,1) as UnallocatedCount,
isnull(AllocatedCount,1) * 1.0 / isnull(UnallocatedCount,1) * 1.0 as Ratio,