SELECT语句比率计算异常：NULL转1后无法得出结果

你的核心问题是：计算Ratio时用的是原始的null计数字段，而非你用CASE处理后显示的1。

SQL的SELECT子句中，字段别名不能在同层级的其他计算中直接引用——你虽然在显示时把AllocatedCount和UnallocatedCount转成了1，但计算Ratio时调用的还是子查询/原始查询里的null值，自然得到null结果。

错误写法示例（对应你的场景） #

SELECT 
    PersonName,
    CASE WHEN AllocatedCount IS NULL THEN 1 ELSE AllocatedCount END AS AllocatedCount,
    CASE WHEN UnallocatedCount IS NULL THEN 1 ELSE UnallocatedCount END AS UnallocatedCount,
    -- 这里用的是原始的null字段，不是上面的别名
    AllocatedCount / UnallocatedCount AS Ratio
FROM (
    SELECT 
        PersonName,
        COUNT(CASE WHEN Status = 'Allocated' THEN 1 END) AS AllocatedCount,
        COUNT(CASE WHEN Status = 'Unallocated' THEN 1 END) AS UnallocatedCount
    FROM your_table
    GROUP BY PersonName
) t

两种正确解决方式 #

方式1：在子查询中提前处理null（推荐）

用COALESCE函数（比CASE更简洁）把null替换为1，后续直接用处理后的字段计算Ratio：

WITH PersonCounts AS (
    SELECT 
        PersonName,
        -- 计数为null时替换为1
        COALESCE(COUNT(CASE WHEN Status = 'Allocated' THEN 1 END), 1) AS AllocatedCount,
        COALESCE(COUNT(CASE WHEN Status = 'Unallocated' THEN 1 END), 1) AS UnallocatedCount
    FROM your_table
    GROUP BY PersonName
)
SELECT 
    PersonName,
    AllocatedCount,
    UnallocatedCount,
    -- 直接用处理后的数值计算，避免null
    CAST(AllocatedCount AS FLOAT) / UnallocatedCount AS Ratio
FROM PersonCounts

注：加CAST(xxx AS FLOAT)是为了避免整数除法（比如1/1得到1.0而非1，如果你需要小数结果的话）

方式2：计算Ratio时重复处理逻辑

如果不想用CTE，也可以在Ratio计算中直接重复COALESCE（或CASE）的处理逻辑：

SELECT 
    PersonName,
    COALESCE(AllocatedCount, 1) AS AllocatedCount,
    COALESCE(UnallocatedCount, 1) AS UnallocatedCount,
    -- 直接对原始字段做null替换后计算
    CAST(COALESCE(AllocatedCount, 1) AS FLOAT) / COALESCE(UnallocatedCount, 1) AS Ratio
FROM (
    SELECT 
        PersonName,
        COUNT(CASE WHEN Status = 'Allocated' THEN 1 END) AS AllocatedCount,
        COUNT(CASE WHEN Status = 'Unallocated' THEN 1 END) AS UnallocatedCount
    FROM your_table
    GROUP BY PersonName
) t

额外说明 #

COUNT(CASE ...)返回的是0还是null？如果你的分组中没有对应状态的数据，COUNT(CASE WHEN Status='xxx' THEN 1 END)会返回0而非null——如果是这种情况，你可能需要替换的是0而非null，这时候把COALESCE(xxx,1)改成CASE WHEN xxx=0 THEN 1 ELSE xxx END即可。

内容的提问来源于stack exchange，提问作者yush